topic Re: How to write script to count the last 30 days for each date and load into temp table in App Development

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

QlikSenseLearner — Fri, 12 Aug 2022 11:17:27 GMT

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:

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

Aurelien_Martinez — Fri, 12 Aug 2022 14:28:24 GMT

Hi,

I have tryed with this little example (last 2 days). I'm using intervalmatch for generating all 2 days interval and after than I sum the data.

Data:
Load
Date*1 as Date,
val
Inline [
Date, val
05/01/2022, 5
05/02/2022, 10
05/03/2022, 15
05/05/2022, 20
];

Left Join(Data)
IntervalMatch(Date)
Load
Date - 1 as Deb, //number of last days
Date*1 as DateFin
Resident Data
;

Load
DateFin,
Sum(val) as total
Resident Data
Group By
DateFin
;

Drop Table Data;

Aurélien

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

QlikSenseLearner — Tue, 16 Aug 2022 09:48:30 GMT

Thank you but I have large data so it took 14 hrs to load the data and finally it failed. It dint work for me.

I have one year data were each day the service id's will be created with the range of 4k-5k . so for each day I have to calculate the count of last 30 days service id's and insert the count to each date .