Pivot Table: Compare Amount for Two Dates

[Ankhi]

Hi 

Can any one please help me with the below requirement?

I have a pivot table with dimensions Category and Programme.

The measures are Actuals,TRC, Actuals + TRC,  Actuals+TRC (to be compared). There is a date filter in the sheet called Fiscal_Date.

Expression I am using is 

Actuals=Sum({<
[Cost Status]={'ACT'},
[FISCAL_DATE] = {"<=$(=max(FISCAL_DATE))"}
>}Amount)(as  I want to sum all the amount till the date selected in the filter). 

Similarly I have same expression for TRC. And Actuals +TRC. (Sum of Actuals and TRC)

Now I want to add another date filter e.g Fiscal_Date_Comparision in the sheet. And want to calculate  Actuals+TRC (to be compared) taking the date value from Fiscal_Date_Comparison filter. 

The aim is to show Actuals+TRC for two different dates for comparison in the same table.

Can you please help me to understand how can I add another date filter using the same date field and do the calculation for Actuals+TRC(to be compared)

Many Thanks

Regards

Ankhi

[fosuzuki · Accepted solution]

Hi Ankhi,

you can use a feature called Alternate States: https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Alternate%20Sta...

The idea is that an Alternate State will "duplicate" the data model and selections in this alternate state will not interfere in the default one.

So :

1. create an alternate state "Alt1" (Document Properties>General>Alternate States button)

2. create a list box and use the same Fiscal_Date field

3. set this filter pane to "Alt1" (Object Properties>General>Alternate State dropdown)

4. add an expression to the pivot table by using set analysis such as:

Sum({<
[Cost Status]={'ACT'},
[FISCAL_DATE] = {"<=$(=max({Alt1} FISCAL_DATE))"}
>}Amount)

[Ankhi]

Hi Fosuzuki,

Thanks a lot for the reply. I  tried and it is working. 

Can you please let me know how to update the label using it. For e.g for the 1st Amount + TRC

I am using label like 

= Right([FISCAL_DATE],6) & ' Actuals'.

Similarly for the new expression as you have mentioned below I would also like to label it using the above expression so that the users know which date it is comparing against.

= Right({Alt1}[FISCAL_DATE],6) & ' Actuals'. But when I use this it gives me an error .

Please help.

Regards

Ankhi

[fosuzuki]

Why are you using the Right() function? What the date looks like in the field?

[Ankhi]

Hi Fosuzuki,

Thanks again for getting back to me.  The date looks like 01-AUg-20 . I want to show only AUG-20 thats why I was using the right function. 

But even without the right function it is giving me error in expression.

I am writing this expression in the Label .

=({Alt1}[FISCAL_DATE]) & ' EAC'

Regards

Ankhi

[fosuzuki · Accepted solution]

I see.

So I think you should use:

right(max(FISCAL_DATE),6) & 'XXX'

and

right(max({Alt1}FISCAL_DATE),6) & 'XXX'