topic Re: Trend in Pivot table with Value list not working in App Development

Trend in Pivot table with Value list not working

madnanansari — Wed, 23 Aug 2017 15:45:54 GMT

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

madnanansari — Wed, 23 Aug 2017 16:03:02 GMT

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

sunny_talwar — Wed, 23 Aug 2017 16:07:57 GMT

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

Re: Trend in Pivot table with Value list not working

madnanansari — Thu, 24 Aug 2017 10:43:01 GMT

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

sunny_talwar — Thu, 24 Aug 2017 11:24:05 GMT

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

madnanansari — Thu, 24 Aug 2017 14:18:53 GMT

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

Re: Trend in Pivot table with Value list not working

sunny_talwar — Thu, 24 Aug 2017 14:44:52 GMT

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

madnanansari — Thu, 24 Aug 2017 16:28:27 GMT

pls. find attached the result app.

Re: Trend in Pivot table with Value list not working

sunny_talwar — Thu, 24 Aug 2017 19:02:45 GMT

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