mtd,ytd ,day calculation

[Anonymous]

Hi All,

I am trying to calculate mtd ytd and day calculations.Below is the code .

Actually i am not getting the desired results .Please correct if i am wrong.

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

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

Cal:

LOAD

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

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

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

left Join(Cal)

load date(TempDate,'YYYYMMDD') as DATE_KEY

Resident Cal;

NoConcatenate

Cal_TimeGranule:

Load *, 'YTD' as TimeGranule

Resident Cal where DATE_KEY>= YearStart(TempDate) and DATE_KEY<=TempDate ;

//Bringing Accumulated MTD granule

Concatenate

Load *, 'MTD' as TimeGranule

Resident Cal where DATE_KEY >=monthstart(TempDate) and DATE_KEY<=TempDate;

//Bringing Accumulated Day granule

Concatenate

Load * , 'Day' as TimeGranule

Resident Cal

where DATE_KEY=TempDate;

My requirement is to calculate mtd ytd day calculation.

i have list box which consists of mtd ytd day .One calendar object .consists of date .

And text box object.calculates sum of gross sales.

By selecting date i need to get mtd ytd day values in text box.

[MK_QSL]

Not sure what you want... but you can use something like below...

Let varMinDate = Num(YearStart(Today()));
Let varMaxDate = Num(YearEnd(Today()));


Cal:
LOAD
  *,
  Date(Date,'YYYYMMDD') as DATE_KEY;
LOAD
  $(varMinDate) + Iterno()-1 As Num,
  Date($(varMinDate) + IterNo() - 1) as Date
AutoGenerate 1 
While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Cal_TimeGranule:
Load 
  Date, 
  If(InYearToDate(Date,Today(),0) = -1, 'YTD') as TimeGranule 
Resident Cal;


Concatenate
Load 
  Date,
  If(InMonthToDate(Date,Today(),0) = -1,'MTD') as TimeGranule
Resident Cal;

[Anonymous]

Hi

I want to display list box wth mtd ytd day values.I need mtd ytd day values in one list box.

I am getting values for date selections from calendar.

But i am getting correct values for day mtd ytd selections.

I think my mtd ytd day calculations are wrong,.

please correct me '

Thanks

[MK_QSL]

Please provide your sample application to work.

[Anonymous]

Hi

I attached the document in discussion i have started.Please look into the question asked by me you will find the attachment .

Thank You

[qlikviewwizard]

Hi Check these links.

Standard Calendar, Variables and Date functions for YTD, QTD, MTD

Previous YTQ, QTD, MTD and WTD

MTD, YTD , Moving Totals / Rolling periods as a dimensional items