8 Replies Latest reply: Aug 24, 2017 3:02 PM by Sunny Talwar

Trend in Pivot table with Value list not working

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)

)

• Re: Trend in Pivot table with Value list not working

I tried making simple pivot using value list and it don't work with the month trend. Without value list it works.

• Re: Trend in Pivot table with Value list not working

Would you be able to share a sample to show the issue?

• Re: Trend in Pivot table with Value list not working

Pls. find attached the file.

In the sample dashboard, My requirement is if the user selects any month, it should show the figures for all month during the year.

• Re: Trend in Pivot table with Value list not working

Its a weird behavior... I would suggest creating an island table and use that instead of ValueList

Dim:

LOAD * Inline [

Dim

Sales

COS

];

Now Dim will be your new dimension and use an expression like this

Pick(Match(Only({1} Dim), 'Sales', 'COS'), \$(vSalesCY), \$(vCOSCY))

• Re: Trend in Pivot table with Value list not working

this is not working. it gives the same thing as before.

• Re: Trend in Pivot table with Value list not working

Can you attach the updated qvw file where you have added the Inline Table into the app?

• Re: Trend in Pivot table with Value list not working

pls. find attached the result app.

• Re: Trend in Pivot table with Value list not working

I am not sure why we are seeing this behavior... this might have been a new bug because I have never seen this before....