Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

mtd,ytd ,day calculation

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.

5 Replies
Highlighted
MVP
MVP

Re: mtd,ytd ,day calculation

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;

Highlighted
Specialist
Specialist

Re: mtd,ytd ,day calculation

Hi

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

Screenshot - 14-08-2015 , 15_21_01.pngqli.png

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

Highlighted
MVP
MVP

Re: mtd,ytd ,day calculation

Please provide your sample application to work.

Highlighted
Specialist
Specialist

Re: mtd,ytd ,day calculation

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