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: 
Karim_Khan
Creator III
Creator 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]))

KK
4 Replies
settu_periasamy
Master III
Master III

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

Karim_Khan
Creator III
Creator III
Author

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;

KK
settu_periasamy
Master III
Master III

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

Karim_Khan
Creator III
Creator III
Author

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

KK