Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
QlikviewUserCT
Contributor
Contributor

How Can I Use Table's Cell Conditions in Set Analysis Expression

I would like to create a table where one cell shows the max value.
For example, I have a set of data like the following which shows how much spending a person had in a given category:

PersonCategoryYTM DollarsMonthYear% Change Net Spending
MikeShoes$100May201950%
MikeBooks$50May201950%
MaryShoes$100May201950%
MaryBooks$200May201950%

I want a table that shows which people had the largest % increase in spending year to month and also which category had the most year to month (YTM) net spending for that person.
 
% Change Net50%10%
PersonMikeMary
CategoryShoesBooks
 
Except, when I write the set expression for the category cell as something like
Mode({$<Date_num={"$(=Max({$} Date_Num))"},[YTM Dollars Spent]={"$(=Max({$<Date_Num={'$(=Max({$} Date_Num))'}>} [YTM Dollars Spent]))"}>} [Category])

I get something like:
 
% Change Net50%10%
PersonMikeMary
Category-Books
 
I think it's looking for the category that matches the max YTM overall (i.e. Max(YTM) = $200) and not within the set of the other cells. How do I write the set correctly to find the right category (i.e. Max(YTM)=$100 for column 1 [Mike, May 2019] and Max(YTM)=$200 for column 2 [Mary, May 2019])?
Labels (4)
1 Reply
Brett_Bleess
Former Employee
Former Employee

I would have a look at the following Design Blog posts, the last link is the base URL in case none of the others help, you can search that area further on your own to see if you can find something to help.

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.