Previous day sales

[kumar2]

Hi,

we have received new request where we need to show the previous day sales.

By default(Without  selecting the date ) the KPI should the previous day value comparing with the highest date and showing the sum of sales.

for this i have used the below expression it was working fine

if(GetSelectedCount((Date))=0,(sum(if(Date=Max(Total Date,2),sales))),sum(if(Date=Date-1,sales)))

when the user selects the any date the sum of sales should be shown with respective to previous date sales but the above logic is not working it is showing the blank. Please find the below sample data

Kindly request all to please help me...

Thanks,

Date	Sales
9/1/2020	10
9/1/2020	20
9/1/2020	30
9/1/2020	40
9/2/2020	100
9/2/2020	200
9/2/2020	300
9/2/2020	400
9/3/2020	1000
9/3/2020	2000
9/3/2020	3000
9/3/2020	2500

By default it is showing the previous day(9/2/2020) sales as '1000'.

when i am selecting the 9/2/2020 it should the sum of the sales of the 9/1/2020 i.e.,100 but it is showing as blank(Null())

[Kushal_Chawda]

@kumar2  try below

sum({<Date={"$(=date(Max(Date,2)))"}>}sales)

[kumar2]

Thanks for the reply,

But was not meeting the output which i want

No selections on the date  it was showing the previous day sales but when i select the date it was showing the null

what i want  is without any selections on date it should display the previous date sales

when i select the date it will consider that selected date based on that date i want to show the previous date sales

Please find the below table

for example:-

Date	Sales
9/1/2020	10
9/1/2020	20
9/1/2020	30
9/1/2020	40
9/2/2020	100
9/2/2020	200
9/2/2020	300
9/2/2020	400
9/3/2020	1000
9/3/2020	2000
9/3/2020	3000
9/3/2020	2500

By default it is showing the previous day(9/2/2020) sales as '1000'.

when i am selecting the 9/2/2020 it should the sum of the sales of the 9/1/2020 i.e.,100 but it is showing as blank(Null())

[Kushal_Chawda · Accepted solution]

@kumar2  If your Dates are continuous i.e. there is no gap then you can use below set analysis

sum({<Date={"$(=date(Max(Date)-1))"}>}sales) 

If there is gap in your dates, for eg. 9/2/2020 is missing then if you select 9/3/2020 and you want to pick up previous latest available i.e 9/1/2020  then you need to do some scripting. If  you want to show 0 value for 9/3/2020 due to 9/2/2020 is missing then above set analysis will serve your purpose. Look at the below script if you want to pick up the previous latest available date instead of actual previous date

Data:
LOAD date(Date) as Date, 
     Sales
FROM
[https://community.qlik.com/t5/New-to-Qlik-Sense/Previous-day-sales/td-p/1741423]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

Dates:
LOAD FieldValue('Date',RecNo()) as Date
AutoGenerate FieldValueCount('Date');

Left Join(Data)
LOAD Date,
     Previous(Date) as PreviousDate
Resident Dates
Order by Date;

DROP Table Dates;

then you can use the below set analysis

=sum({<Date={"$(=date(Max(PreviousDate)))"}>}Sales)

[kumar2]

Thanks @Kushal_Chawda 

It works perfectly

[Kushal_Chawda · Accepted solution]

@kumar2  please accept the reply as solution so that it will be helpful fir others to refer

[Kushal_Chawda]

@kumar2  you should mark the accept solution for the reply in which I have provided logic