Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

tyagishaila
Valued Contributor

QTD,MTD,YTD

Hi All,

I have attached trading calendar..

on the basis of this calendar How can I get Quarter To Date, Month To Date and Year To Date Sale?

Please help me out.

Thanks,

10 Replies
arulsettu
Honored Contributor III

Re: QTD,MTD,YTD

try like this

YTQ, QTD, MTD and WTD

tyagishaila
Valued Contributor

Re: QTD,MTD,YTD

Thanks for immediate response..

I had tried it...

there are Yearstart() and more functions which work for standard claendar..

for example for any date, suppose today(), yearstart() gives value 01/01/2015, but according to my Trading calendar year start is from 03/03/2015.

This is the problem..

arulsettu
Honored Contributor III

Re: QTD,MTD,YTD

in sample file no sasles data . can you upload with that to check

tyagishaila
Valued Contributor

Re: QTD,MTD,YTD

Hi Arul,

Sales data Table is very large in size, There are two fields useful for this 'DateKey' and 'Amount'.

I have used following script to create qvd:

TradingCalendarNEW:

LOAD "DateKey","DayName","Fiscal Week","Fiscal Year",FiscalQuarter,

  if(FiscalQuarter = 'Q1',1 ,if( FiscalQuarter = 'Q2', 2, if(FiscalQuarter = 'Q3',3, if(FiscalQuarter = 'Q4', 4)))) as FiscalQuarterNum,

     Year(TempDate) AS CalendarYear,

     Month(TempDate) AS CalendarMonth

    ;

SQL SELECT Date as 'DateKey',Date as TempDate,

    DayName,

    [Fiscal Week],

    Quarter as FiscalQuarter,  

    Year as 'Fiscal Year'

FROM QlikViewDB.dbo.TradingCalendar;

STORE TradingCalendarNEW into TradingCalendarNEW.qvd (qvd);

Drop Table TradingCalendarNEW;

and following expression to calculate Sale this quarter,

sum({$<FiscalYearNum= {$(=max(FiscalYearNum))}, FiscalQuarter=, DateNum={">=$(=NUM(Min(DateNum)))<=$(=Max(DateNum))"}  >} Amount)/1000

I am facing problem to find Sale last Quarter,

Thanks,

Highlighted
Partner
Partner

Re: QTD,MTD,YTD

Hi

As far as I understood from your query, you want FiscalYear start from 01-March to 28/29 Feb. Please correct me if its wrong assumption.

You can calculate as follows:

YTD is 01-01-2014 to Selected Date

Expression would be

Sum({<Year=, Month=, Quarter=, Week=, DateField=,

        DateNum={">=$(=Num(YearStart(Max(DateNum), 0, 3 ))<=$(=Max(DateNum))"}>} Sales)

QTD- Quarter to Date

In the place of year use Quarter

Sum({<Year=, Month=, Quarter=, Week=, DateField=,

      DateNum={">=$(=Num(QuarterStart(Max(DateNum), 0, 3 ))<=$(=Max(DateNum))"}>} Sales)

MTD- Month to Date

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

WTD- Month to Date

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(WeekStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

Or you can also Create a FiscalMapping as shown below (Inline / call Excel) and map with your Calander & use FiscalMonthNumber/FiscalQtr.

 

ActualMonthNoActualMonthFiscalMonthNoFiscalQtr
1January11Q4
2February12Q4
3March1Q1
4April2Q1
5May3Q1
6June4Q2
7July5Q2
8August6Q2
9September7Q3
10October8Q3
11November9Q3
12December10Q4

For FiscalYear, you can use - YearName(DateNum,0,3) as FiscalYear


Let me know if it helps you to resolve your problem or anything else I can help you more.

--

Regards,

Prashant P Baste.

buzzy996
Honored Contributor II

Re: QTD,MTD,YTD

PFA,

Partner
Partner

Re: QTD,MTD,YTD

In this case you can map Fiscal calender with your internal date & Makedate for defining ranges....

For example : (ActualMonthNumber = > FiscalMonthNumber ) :  (1 => 11),  (2 => 12),  (3 => 1) ,  (4 => 2)....   (12 => 10)

and yearstart as YearStart(DateFieldName,0.2) as FiscalYear

Use this FiscalMonthNumber & FiscalYear in calculations.

--

Regards,

Prashant P Baste.

tyagishaila
Valued Contributor

Re: QTD,MTD,YTD

Thank you so much for new approach,

Actually Fiscal year start is not depends upon 1st day of month, it takes 1st sunday of Mar-Apr,

in my case here Fiscal Year start from

30/03/2014Sun1Q12014-15

it is different for each fiscal year,

because of this quarterstart, yearstart are not giving correct value.

Mapping can work.. Can you more explain How should I use mapping in this case.

Thanks Again

tyagishaila
Valued Contributor

Re: QTD,MTD,YTD

Actually I used FiscalQuarterNum= {$(=max(FiscalQuarterNum)-1)}

it works but it takes all sale of last quarter but I need Quarter To Date,

Do you have solution of this?

Thanks,