topic Re: Day ,Mtd ,Day in load script in QlikView

Day ,Mtd ,Day in load script

Anonymous — Wed, 19 Aug 2015 10:41:26 GMT

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

Re: Day ,Mtd ,Day in load script

kavita25 — Wed, 19 Aug 2015 10:55:40 GMT

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

Re: Day ,Mtd ,Day in load script

Anonymous — Wed, 19 Aug 2015 10:58:25 GMT

Can you explain a bit in detail

Thanks

Re: Day ,Mtd ,Day in load script

kavita25 — Wed, 19 Aug 2015 11:07:06 GMT

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

Re: Day ,Mtd ,Day in load script

Anonymous — Wed, 19 Aug 2015 11:13:36 GMT

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

Re: Day ,Mtd ,Day in load script

Anonymous — Wed, 19 Aug 2015 11:39:18 GMT

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

Re: Day ,Mtd ,Day in load script

kavita25 — Wed, 19 Aug 2015 11:47:58 GMT

please post a sample data with expected result

Re: Day ,Mtd ,Day in load script

Anonymous — Wed, 19 Aug 2015 12:01:32 GMT

please find the attachment in question i have asked

Thanks

Re: Day ,Mtd ,Day in load script

Anonymous — Wed, 19 Aug 2015 12:15:59 GMT

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;

Re: Day ,Mtd ,Day in load script

kavita25 — Wed, 19 Aug 2015 12:17:58 GMT

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

Re: Day ,Mtd ,Day in load script

Anonymous — Wed, 19 Aug 2015 12:21:37 GMT

When i Click on Mtd or Ytd list box i should get sum(sales) for Ytd and mtd.in a text box.

let's assume if i select mtd in lsit box i want sum of sales for mtd in text box

Re: Day ,Mtd ,Day in load script

kavita25 — Thu, 20 Aug 2015 05:13:55 GMT

please provide you qvd...so that I can test...

and also wat about the calendar object, if I select 1/1/2014 or any date...what should day, mtd and Ytd list box should show??

Is it

for day- the sum of sales for that particular date

for mtd-whole of jan month

for ytd-whole of 2014

Re: Day ,Mtd ,Day in load script

Anonymous — Thu, 20 Aug 2015 05:50:38 GMT

Calendar object is Tempdate.

For day -it should show sum sales for that particular day.

For mtd-if you select 20/01/2014 ,it should show sum of sales from start of that month to 20/01/2014 i.e selected date.

for ytd- if you select 20/01/2014 it should get the sum of sales form yearstart of date to selected date.

if i select any date form caledar object ,you need to select mtd ,ytd,day list box for sum of sales for ytd,mtd,day

Re: Day ,Mtd ,Day in load script

Anonymous — Thu, 20 Aug 2015 05:54:06 GMT

I have attached the qvd in the question (top of page)

Thanks

Re: Day ,Mtd ,Day in load script

Anonymous — Thu, 20 Aug 2015 12:25:00 GMT

Hi Kavitha,

I have corrected the logic in the script

Thanks for your help

Re: Day ,Mtd ,Day in load script

kavita25 — Thu, 20 Aug 2015 12:26:29 GMT

Can u share the script...or qvw...

Re: Day ,Mtd ,Day in load script

Anonymous — Thu, 20 Aug 2015 13:40:23 GMT

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;