Show Highest and Lowest Sales and Day in text box

[qlikwiz123]

I have Dates and Sales Columns in my data.

I need to show what day/date from previous week had highest sales and lowest sales, and how much, and put these numbers in a text box . If anyone could help me with this, I highly appreciate your help.

[Oleg_Troyansky]

Hi there!

This is quite an advanced calculation, isn't it? I won't be able to build it for you within the scope of a forum message, but I'll give you some pointers:

- To calculate the highest and the lowest daily sales, you need to use nested aggregation with the function AGGR - something like this:

{< Set Analysis filtering last week dates>}   Min(AGGR(sum(Sales), Date) 

- To calculate the date in which the highest and the lowest sales were achieved, you need to use the function FirstSortedValue in combination with the same AGGR() as above, as a sorting expression. Something like this:

FirstSortedValue(Date, AGGR({< Set Analysis filtering last week dates>} sum(Sales), Date)) - this is for the lowest sales

FirstSortedValue(Date, - AGGR({< Set Analysis filtering last week dates>} sum(Sales), Date)) - this is for the highest sales

Allow me to invite you to my session on Set Analysis and AGGR at the Masters Summit for Qlik, where I will be teaching some of these advanced techniques of using Set Analysis and AGGR() for advanced analytics. One of my hands-on exercises describes a very similar calculation, even a bit more complex.

[qlikwiz123]

@Oleg_Troyansky 

Thank you.

I am not sure how to this step.

{< Set Analysis filtering last week dates>}   Min(AGGR(sum(Sales), Date) 

This doesn't look like a proper Set analysis. If I have Dates in my Dates column, how do I create a set analysis to filter dates for last week in the above template?

[Oleg_Troyansky]

Hmm, there are many ways of selecting last week. Something like this could work, I suppose:

{<Date={"=Weekstart(Date) = Weekstart(Today())-1"}>}

[qlikwiz123]

Thanks. I figured it would be easy to flag all the dates that are in Last Week and use that as my Set condition.

FirstSortedValue(Date, AGGR(SUM({$< [LastWeek Date]={'-1'}>} (Sales)),Date))

This is my Set Analysis for Max Sales where 'LastWeek Date'=-1 is my flag for last week dates. This does give me some number but that is not maximum sales value from last week. Min Sales does the same thing (shows some random number)

[Oleg_Troyansky]

Qlikwiz123,

This formula should give you the date of the lowest sales. Maybe you need to enclose this formula in the function Date() to see the formatted date.

The actual lowest Sales number can be calculated as min(AGGR( ... ) ), using the same AGGR() function.

Cheers,