Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Karim_Khan
Contributor III

YTD,MTD correct QTD wrong

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]))

4 Replies
Highlighted

Re: YTD,MTD correct QTD wrong

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

Highlighted
Karim_Khan
Contributor III

Re: YTD,MTD correct QTD wrong

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;

Highlighted

Re: YTD,MTD correct QTD wrong

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]))

Highlighted
Karim_Khan
Contributor III

Re: YTD,MTD correct QTD wrong

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