Get the latest value in a pivot table

[WalidChoukri]

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.

[Marcos_Ferreira_dos_Santos]

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

[eddie_wagt · Accepted solution]

You can solve this by using FirstSortedValue function like this

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

[WalidChoukri]

Thank you so much it did work

[WalidChoukri]

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