Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
RobinK
Contributor II
Contributor II

Set Analysis - get the most recent record for each category

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:

Example.png

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 🙂

Labels (1)
8 Replies
hic
Former Employee
Former Employee

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
Contributor III
Contributor III

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

RobinK
Contributor II
Contributor II
Author

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
Master
Master

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
Contributor III
Contributor III

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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! 🙂