New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Cloud Maintenance is scheduled between March 27-30. Visit Qlik Cloud Status page for more details.
cancel
Showing results for
Did you mean:
Creator II

Show Highest and Lowest Sales and Day in KPI

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.

Labels (4)

• Visualization

1 Solution

Accepted Solutions
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
7 Replies
Creator

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

Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Creator II
Author

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

Creator II
Author

@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.

Champion III

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) )

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Creator II
Author

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

Creator II
Author

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.

Tags
Community Browser