Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
subathra
Partner - Contributor III
Partner - Contributor III

Date Generation Issue

Hi all,

I have one year data(Fields are Date,Sales,Item,Branch)...i have to generate all dates between min(Date) and max(Date)..then i have to find cumulative sales for each Date(Order by Date)...

i Tried this.. but it took 2 hours to  Generate cumulative for one month data...is there any Solution to reduce my loading time

EX:(Input)

Date           Sales

1/1/2016    10

3/1/2016    40

4/1/2016    30

6/1/2016    10

Output should be like this

Date           Sales

1/1/2016    10

2/1/2016    10

3/1/2016    50

4/1/2016    80

5/1/2016    80

6/1/2016    90

6 Replies
pradosh_thakur
Master II
Master II

please find the attached.

comment the master calender field you don't want.Capture.PNG

regards

Pradosh

Learning never stops.
prat1507
Specialist
Specialist

Hi

Pleas see the attached app.

Regards
Pratyush

subathra
Partner - Contributor III
Partner - Contributor III
Author

Hi ,

I dont know whether this code reduce the loading time... i will work on it

subathra
Partner - Contributor III
Partner - Contributor III
Author

Hi Pratyush,

I am not getting the cumulative value while executing this code

pradosh_thakur
Master II
Master II

If you want only date keep the date part only . you can delete the others .The code i have taken from Creating A Master Calendar‌ Don't forget to do full accumulation in the expression tab. Which is the main reason behind the accumulation.

  1. Temp: 
  2. Load 
  3.                min(OrderDate) as minDate, 
  4.                max(OrderDate) as maxDate 
  5. Resident Orders; 
  6.  
  7. Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
  8. Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
  9. DROP Table Temp; 
  10.  
  11. TempCalendar: 
  12. LOAD 
  13.                $(varMinDate) + Iterno()-1 As Num, 
  14.                Date($(varMinDate) + IterNo() - 1) as TempDate 
  15.                AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 
  16.  
  17. MasterCalendar: 
  18. Load 
  19.                TempDate AS OrderDate
  20. Resident TempCalendar 
  21. Order By TempDate ASC; 
  22. Drop Table TempCalendar; 
Learning never stops.
subathra
Partner - Contributor III
Partner - Contributor III
Author

hi,

I need to do the accumulation in back end itself