Set Analysis - get the most recent record for each category

[RobinK]

Dear Qlik Community,

I am fairly new to QlikSense and I could use some help here.
Lets say we have the following data:

Data:
LOAD * INLINE [
Quarter,Category,Sales
1,A,100
1,B,250
2,A,150
2,B,300
3,A,300
3,B,350
3,C,300
4,A,400
4,C,350
];

This table displays the total sales per category per quarter.
I now want to create a table, that shows the most recent sales for the selected quarter.

For example, if I filter by quarter 4, I want to see the following values:
- 400 for category A (value from quarter 4)
- 350 for category B (value from quarter 3)
- 350 for category C (value from quarter 4)

To get the quarter with the most recent record per category, i used the expression:
Max({1<Quarter={"<=$(=max(Quarter))"}>}Quarter)

And to get the sales value for the latest record i tried using the expression:
Only({<Quarter={'$(=Max({1<Quarter={"<=$(=max(Quarter))"}>}Quarter))'}>}Sales)

Here is a screenshot of the result:

As you can see, the expected value for category B and quarter 3 is not displayed. It seems like the expression on the right hand side calculates the overall max quarter and not the max quarter per category.
Does anybody have an idea how this could work?

Many thanks in advance!

By the way, this is my first post ever. So if I did something wrong with the tags or something, please let me know 🙂

[hic]

Set analysis is like a selection that is done BEFORE the chart is calculated. So it is not possible to have a selection of Quarter=3 for some categories and Quarter=4 for other categories.

I would use the following, knowing that it would exclude categories that were not sold the latest month:

Sum({<Quarter={'$(=Max(Quarter))'}>}Sales)

Note that I use 'Sum' instead of 'Only'.

[Dataintellinalytics]

Try this  ==>    Only(Aggr(Firstsortedvalue(Sales, -Quarter), Category))

[RobinK]

Thank you guys very much for your responses!

"Only(Aggr(Firstsortedvalue(Sales, -Quarter), Category))" definitely put me one step closer to my desired result. Now if no selection is made, the correct values are shown. However, if I filter by quarter 4 the value for category B disappears again.

[anat]

B record not comes under quarter 4 as per your data set,so when you filter quarter 4 you will see only A,C 

[Dataintellinalytics]

You can ignore the Quarter selection or simply ignore every thing. Please try the below.

Only({<Quarter= >} Aggr( {<Quarter= >} Firstsortedvalue(Sales, -Quarter),Category)) 

OR

Only({1} Aggr( {1} Firstsortedvalue(Sales, -Quarter),Category))

[RobinK]

Thanks again for your suggestion and sorry for the late response, but I didn't have the chance to look into this thread until now.

By ignoring the quarter selection it is unfortunately not possible to show the latest value per category for a specific quarter.

I tried your expressions with different filter-variations, but it either shows the overall latest values, no matter which quarter is selected, or categories without a value for the selected quarter just disappear, instead of showing the latest value up until the selected quarter.

I found a way to solve my problem by modifying the data in the script editor, but I am really interested, if this would be also possible without having to change the data.

[RobinK]

Yea that's right. I'm wondering if there is any way to show the latest value for B, even if there is no record in the selected quarter. So if the latest value for B is in quarter 3 it should show this value, when quarter 4 is selected.

[RobinK]

Okay I think I found a solution.

This one seems to work: Firstsortedvalue({<Quarter={"<=$(=max(Quarter))"}>}Sales, -Quarter).

Thanks to everyone for your input and help! 🙂