Showing posts with label sales. Show all posts
Showing posts with label sales. Show all posts

Thursday, March 29, 2012

Calculated Member help

Hi, I have a calculated member that computes the Sales per week for any given level. I am using a time dimensiona and a sales dimension. The time dimension has these levels: Year, Quarter, Month, and Weeks. I am using this formula

[Measures].[Sales]/Descendants([TIME_WEEK_DIM],[TIME_WEEK_DIM].[Time Week Desc]).count

The problem with this is that it works only if the user select a single time member. I am using Excel as frontend and I fixed the pivot table in such a way that Sales is on the column, and Store is on the Rows. The time dimension is on the top serving as filter (Page axis). Because you can choose multiple dimension member in the dropdown boxes of any dimension in Excel, I am having trouble making it work in these scenario. It only works if you select one time member at a time. Please help.I don't fully understand what you are trying to do, but since the database server is returning a simple dataset to your Excel pivot table, the problem would appear to be with the pivot table and not the query or table you are using. When you filter the pivot table you are fiiltering its stored snapshot of the query results. It does not pass the parameters back to the database server.

blindman|||Hi, thnks so much for the reply. Anyway, to make it clear to you:

I'm using an Analysis Service Cube as my backend and Excel Pivot table as my frontend.

I have two dimension in my cube: 1. Time(Yr, Qtr, Month, Week) and 2. Store

I have two measures: 1. Sales and 2. Sales per week (this measure is a calculated member

The formula I use to make the Sales per week measure is:
[Measures].[Sales]/Descendants([TIME_WEEK_DIM],[TIME_WEEK_DIM].[Time Week Desc]).count

Here's my setup in Excel: The sales is broken down by store and the time is on top acting as a filter

Time: 2003

Store Sales Sales/Week
Store1 100 25
Store2 200 12

My problem occurs evrytime the user selects more than one Time value to filter on (like all Jan AND Feb data). It works only if one time value is selected at a time. When user chooses more than 1 time value, the Sales/Week data appears as: #Num!|||Sorry smb, but I don't have experience with data cubes syntax. Hopefully now that your post is at the top of list someone else will check it out and give you an answer!

blindman|||Thnks anyway blindman :)

Tuesday, March 27, 2012

Calculated member - please help (SQL Server 2000)

hi,

please if u could help with this:

i have a cube with 3 dimensions, Sales (fact table), Time and Accounts (general ledger accounts). Accounts dimension hierarchy is as follows:

1. Revenues
1.1 Income
1.1.1 number of account in General Ledger
1.1.2 number of account in GL etc.....
2. Expenses
2.1 Fixed costs
2.1.1 number of account in GL etc....
2.2 Operating costs
2.3 Overhead
3. Unsorted

now what i need to do is next: i need to calculate measure.salesamount in percentage of total sales amount for each category of accounts. for example i need measure.salesamount of each account number in Revenue/Income in percentage of the total salesamount of the category Revenue.

i hope i was clear enough in what i need.

please can anyone help me with this?

thank you in advance!

marko

try an expression something like the following in your calculation:

Measures.salesAmount / (Measures.salesAmount,Accounts.CurrentMember.Parent)

You can then either wrap the whole thing in brackets and mulitply by 100 or you should be able to use a percentage style format string - it depends a bit on how your front end works.

|||Hello Marko,

i am not 100% clear on ur pblm. but on what ever i understood i have given u a formula for calculating the calculated-member.

iif(
[revenue_dimension].currentmember.parent is null,1, ( [revenue_dimension].currentmember , [measures].[salesamount] ) / ([revenue_dimension].currentmember.parent , [measures].[salesamount] )

)
then make the data type of this calculated-member to percentage.

regards,
Rajiv.|||

Hi,

thank you both for your answers. both formulas work and give the same result.

Thank you once again for your help! Much appreciated!

best regards

marko

Sunday, March 25, 2012

Calculated Measure for Sales Data

Hello!

I'm sort of a novice with AS, so you guys may have to bear with me. We have a sales database created in SQL 2000 and OLAP cubes built against it with AS 2000. The cubes contain dimensions of Customers, Products, Calendar, and measure of NetSales. We have numerous other dimensions and measures.

I'm wanting to build a calculated measure for WeeksOnHand. This measure is supposed to only show the items which represent the particular member chosen in the WeeksOnHand measure. For instance, WeeksOnHand would include the following members 1 through 10. Each number represents number of weeks. The number of weeks defines how many weeks a product has been onhand (netsales greater than zero).

So, if I were to filter on Customer A, choose all Products, and choose 2 in the WeeksOnHand measure, it would only show me the products for Customer A which have been onhand for 2 weeks (in other words, sales greater than zero for 2 weeks). It would filter out any products in which sales started occurring longer than 2 weeks, less than 2 weeks, or still zero).

Any help would be appreciated.

Thanks!

Lance

Good Morning Everyone!

No takers?

Calculated Measure for Sales Data

Hello!

I'm sort of a novice with AS, so you guys may have to bear with me. We have a sales database created in SQL 2000 and OLAP cubes built against it with AS 2000. The cubes contain dimensions of Customers, Products, Calendar, and measure of NetSales. We have numerous other dimensions and measures.

I'm wanting to build a calculated measure for WeeksOnHand. This measure is supposed to only show the items which represent the particular member chosen in the WeeksOnHand measure. For instance, WeeksOnHand would include the following members 1 through 10. Each number represents number of weeks. The number of weeks defines how many weeks a product has been onhand (netsales greater than zero).

So, if I were to filter on Customer A, choose all Products, and choose 2 in the WeeksOnHand measure, it would only show me the products for Customer A which have been onhand for 2 weeks (in other words, sales greater than zero for 2 weeks). It would filter out any products in which sales started occurring longer than 2 weeks, less than 2 weeks, or still zero).

Any help would be appreciated.

Thanks!

Lance

Good Morning Everyone!

No takers?

Calculated measure for avg sales over last 3 years

Hello,

I am new at MDX and I want to create a calculated measure which gets the avg sales qty over the last 3 years. The average has to be calculated as of today's date (so with the Now() statement the system date is being fetched).

I have a facttable with all the sales quantities and i have a time table which contains the levels year, quarter, month and day.

Does anyone know how to do this? Thanx in advance.

Greetings,

Patrick

Patrick,

IF your last member in date dimension does not represent last loaded day, then you will have to use NOW() to get Current date. Example of such query depends on your date format. You can use date day key or name to generate member using format function.

Lets say your Day Key has format YYYYMMDD, then this will give you current day member:

StrToMember([Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")

So average 3 last years (AVG([Date 3 year ago]: [Todays Date], Measure)

CREATE MEMBER CurrentCube.Measures.[Avg3Years] AS

Avg(

{ParallelPeriod( [Date].[Date].[Date Yr], 3, StrToMember([Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]"))

: StrToMember([Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")}

, [Measures].[Sales Qty]

) ;

Vidas Matelis

|||

Hi Vidal,

Thanx for the answer. I can't get it to work though. The following part gives a Token not valid error:

StrToMember([Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]"))

: StrToMember([Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")

Another thing is. In my date hierarchy I have the last day. So if I wanted to get it from there how would it work then?

Greetings,

Patrick

|||

Patrick,

This is code is just an example. You would have to substitute your date hierarchy name and your date format.

Is your date key in the format YYYYMMDD? If no, what format is it? And what format is your date name? Also what is your date dimension hierarchy levels?

Vidas Matelis

|||

Hello Vidas,

Ik changed the hierarchy name like under:

Avg(

{ParallelPeriod([Period].[Year], 3, StrToMember([Period].[Year].&[" + Format(now(), "yyyyMMdd") + "]"))

: StrToMember([Period].[Year].&[" + Format(now(), "yyyyMMdd") + "]")}

, [Measures].[Qty sales abs.])

I get the follwing error when trying to parse this:

Formula error - Syntax error - token is not valid: "{ParallelPeriod([Period].[Year], 3, StrToMember([Period].[Year].&[ + Format(now(), "yyyyMMdd") + "]^"^))

: StrToMember([Period].[Year].&[" + Format(now(), "yyyyMMdd") + "]")}

, [Measures].[Qty sales abs.])

Best regards,

Patrick

|||

Patrick,

Could you please try first this simplified query:

SELECT {[Measures].[YourMeasureName]} ON 0
, {StrToMember("[Period].[Year].&[" + Format(now(), "yyyyMMdd") + "]") } ON 1
FROM [YourCubeName];

This query should return todays values. If it is not, you can focus on fixing this query instead of more complicated one.

Most importantly here: do you date members with key defined in the format YYYYMMDD, example: 20070507? If no, you would have to adjust format function to match your format. Examples for that:

Format(now(), "MMMM dd, yyyy") : May 07, 2007

Format(now(), "MMMM d, yyyy") : May 7, 2007

...

Some other formating options:

M - one or 2 digits. Example: 1, 11
MM - Month 2 digit, Example: 01, 11
MMM - Month 3 characters, Example: Jan, Nov
d - day one or 2 digits, Example: 1, 11
yy - year last 2 digits, Example: 07
yyyy - year 4 digits, Example: 2007
....

|||

Hi Vidas,

I managed to get it working now. The wrong formatting caused my problems. Furthermore I used a combination of avg and LastPeriods to get to the result, but you pushed me in the right direction.

Thanx for the help.

Greetings,

Patrick

Thursday, March 22, 2012

Calculate Yearly Sales Difference

Hello,
I've been using Crystal Reports a little bit for about a year now, but have never created a report like this.

The report I am working on displays:
CUSTOMER
2005 SALES $000000.00
2006 SALES $000000.00
TOTAL SALES $000000.00
$ DIFFERENCE $000000.00
% DIFFERENCE $000000.00

I am able to bring up the yearly sales and total sales, but I cannot figure out how to calculate the difference. Any help will be greatly appreciated.

Thank you.I think you must create a query in command query in database expert:
one command query for select sum(sales) from tablename where year=2005 group by year... also another command query for 2006 and compute the difference between the two field in report using formula.

i know it is not nice suggestion but if you have no other idea then try this.|||Thanks for the idea.
I'll take a look and see if I can get it to work.sql

Tuesday, March 20, 2012

Calculate the values from a underlying table

I am building a customerlist within the customer sales of a period. I have a dataset with two tables: "customer" and "customer_ledger_entry".

In the report I will present the customer number, the customer name and the sales. The problem is the sales value is not available as a field, but I have to calculate this value from the "customer_ledger_entry" table. In this table are several entries (invoices, credit notes, etc.)

How to calculate the values from a underlying table?

Here is a post that may work. You would populate the sales value into a dictionary object and then reference it based on the key you decide to use.

(from my blog at http://sqlrs.blogspot.com)

One common problem in reporting and BI solutions is how to incorporate data from both an OLAP cube and relational tables. The UDM in SQL 2005 attempts to solve this, however it really means you still need to build the information into your cubes and dimension attributes.

What if you don't want to or can't?

Reporting Services provides a Custom Code tab within the Report Properties page. You can access various VB.NET objects and system assemblies, and reference external assemblies. One of the internal assemblies is the Dictionary object.

Steps to lookup values from a reference table in SQL:

Drag a list onto the report.
Drag a textbox into the list, or a field from the relational dataset. Modify the textbox to contain =Code.setValue(Fields!KeyField.Value, Fields!ValueField.Value)

Create another list below. Drag another textbox into the list. Modify the textbox expression to hard-code the key for now. =Code.getValue("MyKey")

In the Code Properties window, try the following:

public dict as new System.Collections.Generics.Dictionary(Of System, System)

function setValue(value as object, value2 as object) as object
dict.Add(value,value2)
return value
end function

function getValue(value as object) as object
return dict(value)
end function

Afterwards, you can hide the list box (or table or whatever) that loads the variable with the setValue function. The dictionary still gets populated.

If you have properly bound a table to the first list control, you should be able to lookup results in the second table.

This can be applied in many scenarios, including adding relational reference data to MDX results, and creating a relationship between two separate datasets.

I'd be interested to know if anyone uses this. It seems to have many different applications. One could possibly involve showing two sets of information, for things like variances or budget vs. actual data. If a value doesn't exist in the dictionary, the original field could be returned. If it does exist, the adjustment could be returned.

Note that Generics is .NET 2.0 - for 2000 you may need to use a different syntax but the concept is the same. Basically you're using a dictionary object (could be a hash table or whatever) to store a value by a key. Then you're looking up that value in a table (or list or whatever) to do further calculations.

cheers,

Andrew

|||

Selectis,

I believe you may want to create 2 dataset for your report, the first dataset for the "customer" and the second dataset for "customer_ledger_entry". You can then calculate your values like SUM(Fields!Sales.value,"customer_ledger_entry", customer name like =(Fields!FirstName,"customer")

I hope this was what you were looking for.

Ham

|||

I've made a the two datasets like you said. My expression is:

=Sum(Fields!Sales__LCY_.Value, "customer_ledger_entry"), (Fields!Customer_No_.Value, "customer_ledger_entry") like = (Fields!No_.Value, "customer")

When I run the report I get the message "The Value expression for the textbox refers to a the field "Customer_No_". Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

|||

Selectis,

I must have misunderstood what you were trying to accomplish, I thought you when trying to display values from 2 different tables but I didn’t realize you want to reference the 2 datasets together.

If you can do the following:

You can nest data regions within other data regions. For example, if you want to create a sales record for each sales person in a database, you can create a list with text boxes and an image to display information about the employee, and then add table and chart data regions to show the employee's sales record.

I hope this helps

Ham

|||

I′ve found a way to calculate the value: I use one dataset with two tables: "customer" and "customer_ledger_entry".

The report contains a table and I've used the SUM function for the customer sales: =SUM(Fields!Sales.Value). Now my report calculates all the sales lines from the "customer_ledger_entry". That is not what I want. But the solution is to choose "Edit Group" for the detail line in your report. In the Group settings I added a sorting on the customer and I added a "Group on" expression. Now the SUM function only calculates the records for the customer listed on the line.

Sunday, March 11, 2012

Cal Measure % against total

How to define calculated member whose value should be the row value divided by total of all rows. Example, sales of 2005 was 50k and sales for 2006 was 100k. The % 2005 sales is 33% of total (150k) and 2006 is 67%.

Hi

For example to get the percentage of the Unit Sales for one customer of all customers, you write:

[Measures].[Unit Sales] / ([Measures].[Unit Sales], [Customers].[All Customers])

The key is, to divide by a MDX tuple ([Measures].[Unit Sales], [Customers].[All Customers]) which brings the value for all customers.

Hans

|||

Thank Hans

This problem is solved. Please guide me to get the same result with multiple dimensions.

Also, i have a variance calculated member, which is on the date time diminsion [Sale of 2005] - [Sale of 2006] = [Variance 2005]. I would like to see three measures, [Year 2005 Sale], [Year 2006 Sale] and variance. But i get Sales and variance figure under Year 2005 and same under Year 2006. How to get the desired result.

Thanks

Shekhar

|||

Hi Shekhar,

I'm not sure, if I did understand you right, but I think it's because your [Variance] is on the Time Dimension. I do it in my projects so, that I create a calculated member like:

MEMBER [Measures].[Year variance] AS ([Measures].[Unit Sales],[Time].Currentmember.Prevmember) - [Measures].[Unit Sales]

If Currentmember is Year 2007, den Prevmember is Year 2006 and so on.

If you use now all 3 in a select, you can see it "flat"

select

{ [Measures].[Year 2005 Sales], [Measures].[Year 2006 Sale], [Measures].[Year variance]} on columns,

.....

Hans