topic Re: Get the latest value in a pivot table in QlikView

Get the latest value in a pivot table

WalidChoukri — Mon, 12 Apr 2021 13:42:54 GMT

Hello Everyone,

I have a sample data like below :

and I tried to represent the sum of quantity in a pivot table, the problem is i want to display the latest date data for each month and not the sum,when i collapse the table in each month.

The required values : Article Month Avril Mai
a 15 25
b 13 17

I tried the following expression : Max(Aggr(Sum(Quantity) ,Month,Date,Article)) and i got this.

I need an expression so instead of getting the maximum value for each month i get the latest one, any ideas ?

Thank you so much.

Re: Get the latest value in a pivot table

Marcos_Ferreira_dos_Santos — Thu, 15 Apr 2021 15:54:23 GMT

Hi WalidChoukri

Have you solved this issue ?

I did a test and got it by adding a "flag" column LastDay, filled with yes or no. Then I tried this expression - Sum({<LastDay = {"yes" }>}Quantity) -out and it worked.

That's is just a suggestion, if you wish. Sometimes in Qlik is good to "solve" some issues in script/load time and then have the things more "prepared" for the visualizations.

Best regards

Marcos

Re: Get the latest value in a pivot table

eddie_wagt — Fri, 16 Apr 2021 13:01:57 GMT

You can solve this by using FirstSortedValue function like this

FirstSortedValue(aggr(Sum(Quantity),Date,Article),-Date)

Re: Get the latest value in a pivot table

WalidChoukri — Tue, 20 Apr 2021 10:38:25 GMT

Thank you so much it did work

Re: Get the latest value in a pivot table

WalidChoukri — Tue, 20 Apr 2021 10:40:10 GMT

Thank you, i thought of doing a flag but the file I provided is just a sample, it'll be much difficult to do a flag with the database I have