topic Re: Pivot Table: Compare Amount for Two Dates in QlikView

Pivot Table: Compare Amount for Two Dates

Ankhi — Sat, 16 Nov 2024 00:11:28 GMT

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

Re: Pivot Table: Compare Amount for Two Dates

fosuzuki — Wed, 05 Aug 2020 21:49:53 GMT

Hi Ankhi,

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

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)

Re: Pivot Table: Compare Amount for Two Dates

Ankhi — Thu, 06 Aug 2020 09:28:57 GMT

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

Re: Pivot Table: Compare Amount for Two Dates

fosuzuki — Thu, 06 Aug 2020 14:36:40 GMT

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

Re: Pivot Table: Compare Amount for Two Dates

Ankhi — Thu, 06 Aug 2020 14:54:39 GMT

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

Re: Pivot Table: Compare Amount for Two Dates

fosuzuki — Thu, 06 Aug 2020 17:36:21 GMT

I see.

So I think you should use:

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

and

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