Show Highest and Lowest Sales and Day in KPI

[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 put these numbers in a text box (KPI). If anyone could help me with this, I highly appreciate your help.

[KGalloway]

This may work:

The expressions in the KPI charts are 

Min(if(date >= weekstart(today() - 7) and date <= weekend(today() - 7), sales)) for the minimum

max(if(date >= weekstart(today() - 7) and date <= weekend(today() - 7), sales)) for the maximum

[vinieme12 · Accepted solution]

As below

Lowest Sales

=FirstSortedValue( {< DDT = {">=$(=WeekStart(Max(DDT),-1))<=$(=WeekEnd(Max(DDT),-1))"} >} aggr(sum(Sales) & ' ON ' & DDT ,DDT) ,aggr(sum(Sales),DDT) )

Highest Sales

=FirstSortedValue( {< DDT = {">=$(=WeekStart(Max(DDT),-1))<=$(=WeekEnd(Max(DDT),-1))"} >} aggr(sum(Sales) & ' ON ' & DDT ,DDT) ,  -aggr(sum(Sales),DDT) )

Replace DDT  with your Datefield name

[qlikwiz123]

@vinieme12  Great, this works. Thank you so much.

[qlikwiz123]

@vinieme12  Is it possible to show the overall Day when the average sales are high or low?

If average sales are high on Wednesday, show Wed...like this.

[vinieme12]

Format the date part  using date()

=FirstSortedValue( {< DDT = {">=$(=WeekStart(Max(DDT),-1))<=$(=WeekEnd(Max(DDT),-1))"} >} aggr(sum(Sales) & ' ON ' & Date(DDT,'DDD , DD-MMM-YYYY')  ,DDT) ,aggr(sum(Sales),DDT) )

[qlikwiz123]

Hi @vinieme12  The values are showing '-' in case they have multiple dates with same Highest and lowest sales. How do I handle this?

[qlikwiz123]

@vinieme12 

Not sure if you got a chance to look at my query. Was worried to see '-' and not ideal to show blanks when there are multiple values satisfying the calculation.