topic Re: QTD,MTD,YTD in QlikView

QTD,MTD,YTD

tyagishaila — Thu, 11 Jun 2015 11:39:05 GMT

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,

Re: QTD,MTD,YTD

arulsettu — Thu, 11 Jun 2015 11:42:57 GMT

try like this

YTQ, QTD, MTD and WTD

Re: QTD,MTD,YTD

tyagishaila — Thu, 11 Jun 2015 11:57:36 GMT

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..

Re: QTD,MTD,YTD

arulsettu — Thu, 11 Jun 2015 12:03:46 GMT

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

Re: QTD,MTD,YTD

tyagishaila — Thu, 11 Jun 2015 12:11:34 GMT

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,

Re: QTD,MTD,YTD

prashantbaste — Thu, 11 Jun 2015 12:23:57 GMT

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.

ActualMonthNo	ActualMonth	FiscalMonthNo	FiscalQtr
1	January	11	Q4
2	February	12	Q4
3	March	1	Q1
4	April	2	Q1
5	May	3	Q1
6	June	4	Q2
7	July	5	Q2
8	August	6	Q2
9	September	7	Q3
10	October	8	Q3
11	November	9	Q3
12	December	10	Q4

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.

Re: QTD,MTD,YTD

buzzy996 — Thu, 11 Jun 2015 12:25:03 GMT

PFA,

Re: QTD,MTD,YTD

prashantbaste — Thu, 11 Jun 2015 12:30:19 GMT

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.

Re: QTD,MTD,YTD

tyagishaila — Thu, 11 Jun 2015 12:30:48 GMT

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/2014

Sun

2014-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

Re: QTD,MTD,YTD

tyagishaila — Thu, 11 Jun 2015 12:42:58 GMT

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,

Re: QTD,MTD,YTD

Karim_Khan — Wed, 05 Aug 2015 13:52:26 GMT

Hi Prashant,

Pls help

while am applying the thing on set expression for QTD calculation its not working properly.

Sum({<Quarter=, Month=, Quarter=, Week=, Date=,DateNum={'>=$(=Num(QuarterStart(vMaxDate), 0, 8))<=$(=vMaxDate)'}>} [Net Bookings])

Pls check my below script

Cisco:

LOAD [End Customer Global Ultimate Name],

Country,

[End Customer HQ Top],

[Sales Level 1],

[Sales Level 2],

[Sales Level 3],

[Sales Level 4],

[Sales Level 5],

[Partner Name],

[Fiscal Period ID],

Year(date#([Fiscal Period ID],'YYYYMM')) as FiscalYear,

num(month(date#([Fiscal Period ID],'YYYYMM'))) as FiscalMonthNum,

month(date#([Fiscal Period ID],'YYYYMM')) as FiscalMonth,

Date(date#([Fiscal Period ID],'YYYYMM'),'DD-MM-YYYY') as Date,

FMV,

[Net Bookings],

Group

FROM

[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Assign_Project\Nikhil.G\CISCO\Copy of June raw.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join

LOAD * Inline [

FiscalMonthNum,CalenderMonth, CaldenderMonthNum,FiscalQuarter

1,Aug,8,Q1

2,Sep,9,Q1

3,Oct,10,Q1

4,Nov,11,Q2

5,Dec,12,Q2

6,Jan,1,Q2

7,Feb,2,Q3

8,Mar,3,Q3

9,Apr,4,Q3

10,May,5,Q4

11,Jun,6,Q4

12,Jul,7,Q4 ];

//Final:

//LOAD *,

//monthname(Date#(CalenderYear&CalenderMonth,'YYYYMMM')) as CalenderMonthYear,

//date(Date#(CalenderYear&CalenderMonth,'YYYYMMM'),'DD-MM-YYYY') as CalenderDate;

//LOAD

//FiscalYear,

//FiscalMonthNum,

//CalenderMonth,

//[Fiscal Period ID],

//if(CaldenderMonthNum>=8,FiscalYear-1,FiscalYear) as CalenderYear

//Resident Cisco;

//////

//DROP Table Final;

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(Date) as minDate,

max(Date) as maxDate

Resident Cisco;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

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

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

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

MasterCalendar:

Load

TempDate AS Date,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CYTD,

YeartoDate(TempDate,-1)*-1 as LYTD,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as Rolling12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Regards,