Adding static expressions to a pivot table

[bhsharm5]

Hi,

I have a pivot table like below, where I am measuring some performance by Region and State over every day of the selected month. Now I want to add three static measures/expressions here (which are not affected by the Date dimension on the column) to show performance in same month last year (say Oct 2019), full year 2019 and MTD performance ( Oct 2020).

If I simply add those expressions, the Date dimension on column doesn't let them be in a single column. I am expecting the output to be something like the image below - three additional columns along with the pivot above. Any suggestions, how to achieve this!

Regards,

Bhaskar

[MayilVahanan · Accepted solution]

Hi @bhsharm5 

In order to achieve like attached image, you need to bring on dummy field in "Script" like below
Dim1:
Load * inline

[

Dim

1

2

3

4

];

Then use the Dim in the chart Dimension instead of Date field like below
Pick(Match(Dim,1,2,3,4), 'MTD 2019', 'Full Year', 'MTD 2020', Date)

Then In expression, you can write like below
Pick(Match(Dim,1,2,3,4), Your MTD 2019 formula, Your Full Year Formula, Your MTD 2020 Formula, Your current expression)

Hope it helps

[bhsharm5]

My Date field is dynamic - based on what month user selects, it gives all the dates for the month. So, I can't put all the dates in the custom dimension I create in the script.

Is there a way I create a custom dimension like below, but while using it, the value "Date" actually works like a dimension and gets expanded.

TestDim:
load *Inline
[TestDim
MTD 2019
MTD 2020
Date
];

Currently, I am getting result like this - but instead of "Date", I want actual dates for the selected months

Regards,

Bhaskar

[MayilVahanan]

Hi 
Did you try with my solution?? 
If its not work, Can you attach the sample data and sample qv file. 

[bhsharm5]

Thanks mate, it’s working 👍🏻

Regards,
Bhaskar