Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

eaphymaoweave
New 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
Valued Contributor II

Re: Dimension Set Analysis? Maximum week for a table

hi

may be this

dimension

Store

mesure

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

5 Replies
brunobertels
Valued Contributor II

Re: Dimension Set Analysis? Maximum week for a table

hi

may be this

dimension

Store

mesure

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

Re: Dimension Set Analysis? Maximum week for a table

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

Re: Dimension Set Analysis? Maximum week for a table

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.

mjmilian
New Contributor III

Re: Dimension Set Analysis? Maximum week for a table

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,

Re: Dimension Set Analysis? Maximum week for a table

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

Community Browser