Freeze data in first column of Pivot table QlikSense

[onkarkulkarni]

Hi experts,

I am working on a requirement of Pivot table. I have to display Country sales for current year and prior year(user selectable from filter).

There will be only one entry for current year sales in data and multiple entries for prior year sales. So user wants a filter on screen to select any prior year. Right now when I am selecting Prior year from drop down current year sales is getting disappeared. Please suggest how can I freeze current year sales and reflect prior year sales as per the drop down selection.

.

[ChiragPradhan]

Hi

I think the solution is to have two expression; one for current year and the second one for the selected prior year. 

Find attached an example app. I have used a variable to default the prior year if user has not selected a prior year.

If it is what you are looking for, please mark it as resolved.

Regards,

Chirag

[onkarkulkarni]

Hi Chirag,

Thanks for the reply. 

Your solution seems logical,  somehow its not working at my end. I tried with two different expressions only. Not sure if I am making any mistake in expression. I am using another column Period Flag which is having 2 values , 'N' for current year and 'Y' for all previous years. My expressions are like:

Current Year: Sum(if(ColumnA='ABC' and Period_flag='N', (Sales)))

Prior Year: Sum(if(ColumnA='ABC' and Period_flag='Y', (Sales))) (by default this column should show Current-1 year's sales, user can select other prior period from drop down filter)

Is there any issue with my expression? I have used your variable. Please guide.

Thanks. 

[ChiragPradhan · Accepted solution]

Hi Onkar,

The reason your expression does not work is because you have a field reference i.e. ColumnA and Period_flag, which is not aggregated. Sense will evaluate your expression for every dimensional value. So if there are multiple values in the two columns referenced, it will evaluate to null. 

For your expression to work , you would need to do something like this -

=Sum(distinct if(ColumnA='ABC' and Period_Flag='N',  Sales))

The reason set analysis works is because it is like doing a selection but just for this expression.

 Regards,

Chirag

[onkarkulkarni]

Hi Chirag,

Thanks for getting back on this, I will quickly check and confirm.

[developer01]

how can I freeze the totals row in pivot table in cloud edition?