topic Re: YTD,MTD correct QTD wrong in QlikView

YTD,MTD correct QTD wrong

Karim_Khan — Thu, 13 Aug 2015 14:38:58 GMT

Hi Team,

I am able to calculate YTD and MTD on Fiscal Calendar basis.

But my QTD is getting calculated on Indian Calendar basis.Pls check my below expression and suggest.

MTD,

=sum({<Year={2015}, Month = {"$(vCisco)"}>}[Net Bookings])

YTD,

=sum({<Year={2015},Month={">= $(vFiscalYearStartMonth) <= $(vMaxMonth)"},Month={'<=$(vFiscalNum)'}>}[Net Bookings])

QTD,

=num(sum({$<Year = {$(=only(Year))}, Quarter = {$(=only(Quarter))}, Month = {"<=$(=max(Month))"}>} [Net Bookings]))

Re: YTD,MTD correct QTD wrong

settu_periasamy — Thu, 13 Aug 2015 14:54:55 GMT

Hi karim,

Do you have the Separate Field for FiscalQuarter like FiscalyearStartMonth, or Quarter field describe as Fiscal Quarter?

May be you can create the separate field for Fiscal Quarter, then you can use that in your QTD expression..

Re: YTD,MTD correct QTD wrong

Karim_Khan — Thu, 13 Aug 2015 14:59:08 GMT

Hi Settu,

I have created the Fiscal Quarter in Calendar generation I don't have the separate field.Pls check my script and suggest

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 Year,

Month(Date#([Fiscal Period ID],'YYYYMM')) AS Month,

Week(Date#([Fiscal Period ID],'YYYYMM')) AS Week,

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

FMV,

[Net Bookings],

Group

FROM

\\india.eclerx.com\ctrxdata\ARLM_R_DATA\Karim.Khan\Desktop\Sample_File.xlsx

(ooxml, embedded labels, table is Sheet1);

SET vFiscalYearStartMonth = 8;

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:

LOAD

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

//Year(Date) AS Year, // Standard Calendar Year

//Month(Date) AS Month, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName, // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter, // Standard Calendar Quarter

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear; // Fiscal Calendar Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

Re: YTD,MTD correct QTD wrong

settu_periasamy — Thu, 13 Aug 2015 15:09:35 GMT

Hi,

In the script, you have field FiscalQuarter. But, why did you use the Quarter Field in your QTD expression.

may be try this..

QTD

=num(sum({$<Year = {$(=only(Year))}, Quarter = {$(=only(FiscalQuarter))}, Month = {"<=$(=max(Month))"}>} [Net Bookings]))

Re: YTD,MTD correct QTD wrong

Karim_Khan — Thu, 13 Aug 2015 15:35:17 GMT

Thanks a lot sir u solve my big hurdle sir Once again Tnx