I have define the below value list:
valuelist('Sales', 'Cost of Sales', 'GP')
Now I have define a Value formula as below:
if( Valuelist($(vListHeads1)) ='Sales', $(vSalesCY),
if( Valuelist($(vListHeads1)) ='Cost of Sales', $(vCostOfSalesCY),
if( Valuelist($(vListHeads1)) ='GP', $(vGPCY),0
)
)
)
This function works fine.
Now I need to use a pivot table to show the trend by month of these variables. The filter is based on the month. If I have no selection, it shows totals for all months of the current year. but when I select a month, it don't show the totals (by month) for the that year. it just shows that month only.
The below is the formula I have used for vSalesCY.
If(getselectedcount([Month Year])<1,
Sum({$<[Statistics Name]={'Sales - Retail'}, [Year] = {"$(=Year(AddMonths(Today(),-1)))"} ,[Month No]=,[Month Year]=,[Month]=,[MonthID]= >} [Gl Report Month Balance] * Multiplier)
,
Sum({$<[Statistics Name]={'Sales - Retail'}, [Year] = {"$(=Max(Year))"},[Month No]=,[Month Year]=,[Month]=,[MonthID]= >} [Gl Report Month Balance] * Multiplier)
)
