Solved: Day ,Mtd ,Day in load script - Qlik Community

Anonymous · ‎2015-08-19

Hi

I calculated mtd ytd day calculation in script .I tried it in set analysis but my application is getting bigger so we thought to implement mtd,ytd logic in script.

I got values for day ,but for mtd ytd i am not able to pull the data.

My aplication consist of calendar object ,list box and sum(Sales)

I'll select date and mtd or ytd or day from list box and corrresponding sum of sales will be displayed.

My code is below .

Cal:

LOAD Date ($(StartCalendar) + RecNo()-1) as CalDate

AutoGenerate ((today()-1) - $(StartCalendar)+1 );

//Left Join with no keys

Left Join (Cal)

Load CalDate as #_Date_NUM_KEY

Resident Cal;

//Bringing Accumulated YTD granule

NoConcatenate

Cal_TimeGranule:

Load *, 'YTD' as TimeGranule

Resident Cal where #_Date_NUM_KEY >= YearStart(CalDate) and #_Date_NUM_KEY <= CalDate;

//Bringing Accumulated MTD granule

Concatenate

Load *, 'MTD' as TimeGranule

Resident Cal where #_Date_NUM_KEY >= monthstart(CalDate) and #_Date_NUM_KEY <= CalDate;

//Bringing Accumulated Day granule

Concatenate

Load * , 'Day' as TimeGranule

Resident Cal

where #_Date_NUM_KEY = CalDate;

drop Table Cal;

CAL:

Load

CalDate,

// Date(SALES_DATE, 'DD/MM/YYYY') as SALES_DATE_1,

Month(CalDate) as SALES_MONTH,

Year(CalDate) as SALES_YEAR,

MonthName(CalDate) as SALES_MONTHNAME,

'Q' & Ceil(Month(CalDate)/3) AS SALES_QUARTER,

DayNumberOfYear(CalDate) as DayNumberOfYear,

Day(CalDate) as DayNumberOfMonth,

DayNumberOfQuarter(CalDate) as DayNumberOfQuarter,

num(Month(CalDate)) as MonthNum

Resident Cal_TimeGranule;

Thanks

Anonymous · ‎2015-08-20

Hi

Ignore above script beacause fields has changed by me

Below code works fine

Let varMinDate = Floor(WeekStart(Date#('01/01/2014', 'DD/MM/YYYY')));

Let varMaxDate = Floor(WeekEnd(Date#('31/07/2015', 'DD/MM/YYYY')));

Cal:

LOAD

//$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as CalDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

left Join

load CalDate as TempDate

Resident Cal;

NoConcatenate

Time:

load *,'MTD' As TimeGranule

Resident Cal

Where TempDate >= MonthStart(CalDate) and TempDate <= CalDate;

load *,'YTD' As TimeGranule

Resident Cal

Where TempDate >= YearStart(CalDate) and TempDate <= CalDate;

//Bringing Accumulated Day granule

Load * , 'Day' as TimeGranule

Resident Cal

where TempDate=CalDate;

//

CAL:

Load

TempDate ,

Month( TempDate ) as SALES_MONTH,

Year( TempDate ) as SALES_YEAR,

MonthName( TempDate ) as SALES_MONTHNAME,

'Q' & Ceil(Month( TempDate )/3) AS SALES_QUARTER,

DayNumberOfYear(TempDate) as DayNumberOfYear,

Day( TempDate ) as DayNumberOfMonth,

DayNumberOfQuarter(TempDate) as DayNumberOfQuarter,

num(Month( TempDate )) as MonthNum

Resident Time;

Drop Table Cal;

View solution in original post

kavita25 · ‎2015-08-19

Hi,

you can directly create YTD and MTD with the below code:

if(if(Month(date(Floor(CalDate)))<=3,(Year(date(Floor(CalDate)))-1)&'-'&(Year(date(Floor(CalDate)))),(Year(date(Floor(CalDate))))&'-'&(Year(date(Floor(CalDate)))+1))=if(Month(((today())))<=3,(Year(((today())))-1)&'-'&(Year(((today())))),(Year(((today()))))&'-'&(Year(((today())))+1)),1,0) as YTD_Flag,

inmonth(today(),CalDate,0) as MTD_FLAG

Regards,

kavita

Anonymous · ‎2015-08-19

Hi

Can you explain a bit in detail

Thanks

kavita25 · ‎2015-08-19

from the above code you can create YTD flag and MTD flag in the master calendar script.

Anonymous · ‎2015-08-19

Thanks for the reply !!

But my requirement is i want mtd ,ytd,day values in one list box.

by selecting mtd ,ytd,day i want sum of sales in a text box

Thanks

Anonymous · ‎2015-08-19

hi

With your formula i am close to my results .But i need mtd,ytd,day in one list box

kavita25 · ‎2015-08-19

please post a sample data with expected result

Anonymous · ‎2015-08-19

Hi

please find the attachment in question i have asked

Thanks

Anonymous · ‎2015-08-19

with my code i can successfully pull the day's data which is correct .

i am struggling with Mtd and ytd, it is showing incorrect data .

NoConcatenate

Cal_TimeGranule:

Load *, 'YTD' as TimeGranule

Resident Cal where #_Date_NUM_KEY >= YearStart(CalDate) and #_Date_NUM_KEY <= CalDate;

//Bringing Accumulated MTD granule

Concatenate

Load *, 'MTD' as TimeGranule

Resident Cal where #_Date_NUM_KEY >= monthstart(CalDate) and #_Date_NUM_KEY <= CalDate;

kavita25 · ‎2015-08-19

So whats the expected result on click of YTD, MTD from list box.