Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tyagishaila
Specialist
Specialist

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
Master III
Master III

try like this

YTQ, QTD, MTD and WTD

tyagishaila
Specialist
Specialist
Author

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
Master III
Master III

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

tyagishaila
Specialist
Specialist
Author

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,

prashantbaste
Partner - Creator II
Partner - Creator II

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
Master II
Master II

PFA,

prashantbaste
Partner - Creator II
Partner - Creator II

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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,