topic Re: Date Generation Issue in App Development

Date Generation Issue

subathra — Thu, 05 Oct 2017 12:06:45 GMT

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

Re: Date Generation Issue

pradosh_thakur — Thu, 05 Oct 2017 12:21:00 GMT

please find the attached.

comment the master calender field you don't want.

regards

Pradosh

Re: Date Generation Issue

prat1507 — Thu, 05 Oct 2017 12:36:24 GMT

Pleas see the attached app.

Regards
Pratyush

Re: Date Generation Issue

subathra — Thu, 05 Oct 2017 12:38:32 GMT

Hi ,

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

Re: Date Generation Issue

subathra — Thu, 05 Oct 2017 12:45:35 GMT

Hi Pratyush,

I am not getting the cumulative value while executing this code

Re: Date Generation Issue

pradosh_thakur — Thu, 05 Oct 2017 12:52:37 GMT

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.

Temp:
Load
min(OrderDate) as minDate,
max(OrderDate) as maxDate
Resident Orders;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS OrderDate
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Re: Date Generation Issue

subathra — Thu, 05 Oct 2017 13:14:16 GMT

hi,

I need to do the accumulation in back end itself