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

QlikviewUserCT — Sat, 16 Nov 2024 19:09:20 GMT

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:

Person	Category	YTM Dollars	Month	Year	% Change Net Spending
Mike	Shoes	$100	May	2019	50%
Mike	Books	$50	May	2019	50%
Mary	Shoes	$100	May	2019	50%
Mary	Books	$200	May	2019	50%

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 Net	50%	10%
Person	Mike	Mary
Category	Shoes	Books

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 Net	50%	10%
Person	Mike	Mary
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])?

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

Brett_Bleess — Thu, 05 Mar 2020 17:14:37 GMT

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

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

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

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