Sum of sales on date and date minus one day

[miklodepiklo]

Hi experts,

I'm having these data:

load * inline [
Date, Sales
19-1-2024, 1
19-1-2024, 4
16-1-2024, 2
16-1-2024, 7
15-1-2024, 3
14-1-2024, 5
];

My desired table look like this 

Date          | Sum of sales on date | Sum of sales on date minus 1
19-1-2024 |                                 5 |                                              0
16-1-2024 |                                 9 |                                              3
15-1-2024 |                                 3 |                                              5
14-1-2024 |                                 5 |                                              0

How do I get this result in table expression (not load script)?

I have tried function above but then Qlik returns 7 instead of 0 for Date 19-1-2024.

Any help much appreciated.

[pallavi_96]

Hi @miklodepiklo 

You can use below expression in a straight table

if(RowNo()=1,0,fabs(Above(sum(Sales))-sum(Sales))-1)

[miklodepiklo]

Hi Pallavi,

Thanks for your help. It is showing the desired result for the most part, but it does somehow add a mysterious number 1 at the last row. Any ideas where it comes from and how to get rid of it?

Is there a solution that you can think of that is not affected by sorting?

[SunilChauhan]

Dimension date 

1 first Expression Sum(Sales)

2 expression 
if(RowNo()=1,0,Below(Column(1)));  

Hope this do magic for you.

[miklodepiklo]

Hi Sunil,

Thanks for your suggestion. With your expression if there was no day before a date, it doesn't show '0' like it should. Your expression only shifts the value of sum(sales) and puts it in the new column.

[SunilChauhan]

If(Above(date)-Date>1, above(Sum(Sales))-0-1,

IF(above(Sum(Sales))-Sum(Sales)<0,0,above(Sum(Sales))-Sum(Sales)-1))

[SunilChauhan]

try this 

Test:
Load * inline [
Date1,Sales
19-1-2024,1
19-1-2024,4
16-1-2024,2
16-1-2024,7
15-1-2024,3
14-1-2024,5
];
Load*,
iF(subfield(Date1,'-',1)-subfield(Dateminus1,'-',1)=1,1,0) as Flag,PreviousSum-Totalsum-1 as Newsum
;
Load Date1,Totalsum, Previous(Date1) as Dateminus1,Previous(Totalsum) as PreviousSum
;
Load Date1, Sum(Sales) as Totalsum
resident Test group by Date1 Order by Date1 asc;
drop table Test;

take a table  use Dimesion as Date and Expression  SUM(IF(Flag=0,0,PreviousSum))

i tried in qlikview . but should work in Qliksense too

[miklodepiklo]

Thank you Sunil for your help. I was especially interested in using expressions in the straight tabel this put me in the right direction doing it in load script. 

[pallavi_96]

Hi @miklodepiklo 

If we look at the last row of the sum(sales) column, the value is 5 and the number above it is 3, therefore subtracting 5-3 gives us 2 and subtracting 1 from 2 yields 1.

And I believe it should function that way.If my interpretation is incorrect, please let me know.