Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
QlikSenseLearner
Contributor
Contributor

How to write script to count the last 30 days for each date and load into temp table

I am having trouble in loading the last 30 days volume for each date.  I am getting the same count for all the dates. But it should be different count for each date , it should represent the count of serviceid's  for past 30 days prior to each of the dates loaded through Variable. example:

date  30day volume
8/8/2022 11970
8/7/2022 12300
8/6/2022 12890
8/5/2022 11200
8/4/2022 12100

 

Please help me to find the solution. I am using the below script:

Let vMinDate = num(Date#('11/01/2021','MM/DD/YYYY')) ;
let vMaxDate= num(Date#(today(),'MM/DD/YYYY'));

For X=$(vMinDate) to $(vMaxDate)

Let Y= $(X)-30;

NoConcatenate
FinalCount:
Load Sum(ServiceIdCount) as 'TotalCount'
Resident XYZ
where Floor(Date)>=$(Y) and
Floor(Date) < $(X)
;

Let i = peek('TotalCount', 0 ,'FinalCount');

Temp:
Load
'$(X)' as 'Dte', '$(i)' as 'Cnt';


drop Table FinalCount;

Next X

drop table XYZ;

 

I am getting below result:

QlikSenseLearner_0-1660070551250.png

 

 

 

1 Reply
QlikSenseLearner
Contributor
Contributor
Author

Please anyone help me to provide solution for the above issue.