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: 
eaphymaoweave
Contributor II
Contributor II

Dimension Set Analysis? Maximum week for a table

Hello,

I am trying to create a table which will always just show sales for stores for the latest week. Is there anyway to use set analysis on my dimension to only show the sum of sales per store for the maximum of the week #?

An example of the raw data is below:

WeekStoreSales
1Atlanta3
1Austin5
1Boston8
1Chicago5
1Philadelphia9
2Atlanta6
2Austin1
2Boston7
2Chicago1
2Philadelphia1
1 Solution

Accepted Solutions
brunobertels
Master
Master

hi

may be this

dimension

Store

mesure

=Sum({$<Week={$(=max(Week))}>}Sales)

View solution in original post

5 Replies
brunobertels
Master
Master

hi

may be this

dimension

Store

mesure

=Sum({$<Week={$(=max(Week))}>}Sales)

sunny_talwar

If a certain store doesn't have sales in the max week... like this

WeekStoreSales
1Atlanta3
1Austin5
1Boston8
1Chicago5
1Philadelphia9
1New York10
2Atlanta6
2Austin1
2Boston7
2Chicago1
2Philadelphia1


and you would like to get the sales for New York 1st week because that is it's max week... then try this

FirstSortedValue(Aggr(Sum(Sales), Store, Week), -Aggr(Week, Store, Week))

eaphymaoweave
Contributor II
Contributor II
Author

Thanks Bruno, I was hoping there was some way to use set Analysis on a dimension, but I suppose using the set analysis on all the measures will work as well.

MIKIEMILLER
Contributor III
Contributor III

Sunny,

If you don't mind, can you explain what the -Aggr(Week, Store, Week) part of the equation is accomplishing?

FirstSortedValue(Aggr(Sum(Sales), Store, Week), -Aggr(Week, Store, Week))


Thanks,

sunny_talwar

That is helping the expression pick the most recent week per each store. As I pointed in my expression, New York didn't have any data for week 2... for New York the max week is 1. So, if you want to show New York's sale for week 1, you can use FirstSortedValue expression rather than using set analysis.

Best,

Sunny