How can i use the same variable for multiple expressions but with different AGGR?

[hopkinsc]

Hi all,

I have the following variable..

vChart_Annual_Sales

The variable definition is:

if(vTimeSeries='Next 13 Periods',

     sum({<$(vDatePeriod)>}[Forecast Volume]*[Avg Price]),

if(vTimeSeries='Fiscal Year',

     Sum({<Period>}Aggr(If(IsNull($(vForecastSales)), $(vTotalSalestoLastPeriod), $(vAnnualSales)), SubCat, SubCat1, [Item      Description],[Customer Name], Dim )),

if(vTimeSeries='QTR',

     sum({<Qtr={"($(vCurrentQTR)))"},Year={"($(vCurrentYear)))"},Period={"(>$(vReportingPeriod)))"}>}[Forecast Volume]*[Avg      Price])+sum({<Qtr={"($(vCurrentQTR)))"},Year={"($(vCurrentYear)))"},Period={"(<=$(vReportingPeriod)))"}>}[Sales Value]),

sum({<$(vDatePeriod)>}[Sales Value])

)))

I am using this variable in a pivot table which is by Customer Name and Dim.

I have another pivot table which is by SubCat, SubCat1, and [Item Description] and i am wondering if i could use the same variable (or what do i need to change to use the different AGGR?)

here is the expression that i use for the 2nd pivot table..

if(vTimeSeries='Next 13 Periods',

     sum({<$(vDatePeriod)>}[Forecast Volume]*[Avg Price]),

if(vTimeSeries='Fiscal Year',

     Sum({<Period>}Aggr(If(IsNull($(vForecastSales)), $(vTotalSalestoLastPeriod), $(vAnnualSales)), SubCat, SubCat1, [Item      Description],[Customer Name] )),

if(vTimeSeries='QTR',

     sum({<Qtr={"($(vCurrentQTR)))"},Year={"($(vCurrentYear)))"},Period={"(>$(vReportingPeriod)))"}>}[Forecast Volume]*[Avg      Price])+sum({<Qtr={"($(vCurrentQTR)))"},Year={"($(vCurrentYear)))"},Period={"(<=$(vReportingPeriod)))"}>}[Sales Value]),

sum({<$(vDatePeriod)>}[Sales Value])

)))

Its the same apart from not aggregating by Dim.

I know i can setup another variable but i was wondering if its possible to avoid this and using the same.

I have attached a sample.

[sunny_talwar]

Checking this, did the other issue got resolved?

[sunny_talwar · Accepted solution]

Since Dim is an island table... I think it should not have an impact in the second table... are you seeing some issue?

[hopkinsc]

It does work as expected i just need to find out what date field they are going to use.

I am expecting it to be period to be honest as all of their sales and forecast data is by period not date. They asked for Date though when they gave me the requirements  but thinking about it i don't see how that can work when they don't even have dates in their data (i had to ask for a daily calendar!).

If they use dates and they have more than one price change in the same period then that won't work as forecast data is by period (and this is to be used within the forecast data.

Thanks so much for your help, i will mark that post as correct

[hopkinsc]

apologies, i created a dummy dimension called Dim in the product pivot table to match the customer which i forgot to remove before uploading the sample. the problem with the dummy dimension is that when certain products are selected i think it will show the other values in the Dim field.

The reason for the Dim field is to group the products by 'Core', 'Xmas' and 'ALL'. this is needed for the customer table but not the products table.

if i remove the dummy dimension then i get this when using the variable..

[hopkinsc]

I am running some tests and i can't see a problem with using the dummy dimension actually.

Can you see any issues with keeping it in? as you said, it seems to work when left in.

[sunny_talwar]

I don't think it should be a problem

[hopkinsc]

OK i'll try that. sorry for wasting your time!