topic Re: Help urgent in QlikView

Mtd Day Ytd Calculation in script

Anonymous — Thu, 20 Aug 2015 08:07:40 GMT

I have been strugling with this for a long time.

I have list box with mtd,ytd and calendar object.

I need to to calculate sum of sales for mtd,ytd for date selected.

This is my sample code

Date:

LOAD * INLINE [

Date, Sales

01/01/2014, 100

02/01/2014, 200

03/01/2014, 300

];

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

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

Cal:

LOAD

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

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

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

Left Join (Cal)

Load Date as #_Date_NUM_KEY

Resident Cal;

left Join(Date)

Cal_TimeGranule:

Load *, 'YTD' as TimeGranule

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

Load *, 'MTD' as TimeGranule

Resident Cal where #_Date_NUM_KEY >= MonthStart(Date) and #_Date_NUM_KEY <= Date(Date);

drop Table Cal;

Calendar:

Load Date

Resident Cal_TimeGranule;

Mtd,Ytd logic should be implemented in the script not in set analysis.

Any ideas!! i am not getting desired results.

Thanks

Re: Help urgent

Anonymous — Thu, 20 Aug 2015 08:09:36 GMT

Mtd ,ytd should be in one list box.

If i select date and mtd from list box it should display sum of sales for mtd in a text box

Re: Help urgent

Anonymous — Thu, 20 Aug 2015 09:38:09 GMT

Any Ideas !!

Re: Help urgent

Anonymous — Thu, 20 Aug 2015 11:58:05 GMT

Hi All,

Any How i have done with correct solutions.

Fields might change questiion i have asked and fields in answer.

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

Let varMaxDate = Floor(Date#('06/02/2014', 'DD/MM/YYYY'));

Cal:

LOAD

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

Date($(varMinDate) + IterNo() - 1,'DD/MM/YYYY') as TempDate

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

Left Join

load TempDate as Date

Resident Cal;

NoConcatenate

TimeGran:

load *,'MTD' as TimeGranule Resident Cal where

Date >= monthStart(TempDate) and Date<=TempDate

;

Load *,'YTD' as TimeGranule Resident Cal Where

Date >= YearStart(TempDate) and Date<=TempDate;

load *,'Day' as TimeGranule Resident Cal Where

Date = TempDate;

Drop Table Cal;

Calendar:

LOAD Date as TempDate

Resident TimeGran;