YTD,QTD,MTD,WTD IN Qlik Sense

[scotly-victor]

Hi Qlikers,

I wanted to achieve  YTD,MTD,QTD,WTD for Orderdate .

Below given  is my code ,but it didn't work

=if(DateTemp='YTD',sum({<type={'Order'},Year={$(=Max(Year))},Quarter=,Month=,Week=,OrderDate={"<=$(=Max(OrderDate))"}>}Sales),

if(DateTemp='QTD',sum({<type={'Order'},Year={$(=Max(Year))},Month={$(=max(Month))},OrderDate={"<=$(=Max(OrderDate))"},Week=>}Sales),

if(DateTemp='MTD',sum({<type={'Order'},Year={$(=Max(Year))},Month={$(=max(Month))},OrderDate={"<=$(=Max(OrderDate))"},Week=>}Sales),

if(DateTemp='WTD',sum({<type={'Order'},Year={$(=Max(Year))},Quarter={$(=max(Quarter))},Month={$(=max(Month))},Week={$(=max(Week))},OrderDate={"<=$(=Max(OrderDate))"}>}Sales),

sum({<type={'Order'},Year={$(=Max(Year))},Quarter=,Month=,Week=,OrderDate={"<=$(=Max(OrderDate))"}>}Sales)))))

order date start from apr2014 and end in mar2016

Can any one please help me?

[scotly-victor]

Sorry It didn't work

[scotly-victor]

It works  when I gave  Date(AddYears(Max(OrderDate),-1),DD/MM/YYYY)

But Output is Wrong .

Let me Explain Clearly about this issue

For instance ,When select date 01/03/2016 it should show value from 01/01/2016  to 01/03/2016 for ytd

Likewise it should show value from 01/01/2015 to 01/03/2015 for previous ytd.

gwassenaar‌ sunindia‌

[sunny_talwar]

May  be this:

=If(DateTemp = 'YTD', Sum({<type={'Order'},Year,Quarter,Month,Week, OrderDate = {"$(='>=' & Date(AddYears(YearStart(Max(OrderDate)), -1), 'YourDateFieldFormatHere') & '<=' & Date(AddYears(Max(OrderDate), -1), 'YourDateFieldFormatHere'))"}>} Sales),

If(DateTemp = 'QTD', Sum({<type={'Order'},Year,Quarter,Month,Week, OrderDate = {"$(='>=' & Date(AddYears(QuarterStart(Max(OrderDate)), -1), 'YourDateFieldFormatHere') & '<=' & Date(AddYears(Max(OrderDate), -1), 'YourDateFieldFormatHere'))"}>} Sales),

If(DateTemp = 'MTD', Sum({<type={'Order'},Year,Quarter,Month,Week, OrderDate = {"$(='>=' & Date(AddYears(MonthStart(Max(OrderDate)), -1), 'YourDateFieldFormatHere') & '<=' & Date(AddYears(Max(OrderDate), -1), 'YourDateFieldFormatHere'))"}>} Sales),

If(DateTemp = 'WTD', Sum({<type={'Order'},Year,Quarter,Month,Week, OrderDate = {"$(='>=' & Date(AddYears(WeekStart(Max(OrderDate)), -1), 'YourDateFieldFormatHere') & '<=' & Date(AddYears(Max(OrderDate), -1), 'YourDateFieldFormatHere'))"}>} Sales),

Sum(Sales)))))

[scotly-victor]

Thank you very for you assistance & support  sunindia‌ gwassenaar‌.

Both Current Ytd,Mtd ,Qtd,Wtd  and Previous Ytd,Mtd ,Qtd,Wtd  are work fine.

When it comes to fiscal year does it work  ?

My Fiscal Years are  FY 2014-2015 and FY 2015-2016.

[sunny_talwar]

What exactly is the first month of your fiscal year? We might be able to play around with it to make it work.

[scotly-victor]

Fiscal year start date 01/04/2014 to end 31/03/2015

FY 2014-2015

FY 2015-2016

[sunny_talwar]

I guess in your case the only expression that needs to change here is the YTD, else everything will stay the same. Try this:

=If(DateTemp = 'YTD', Sum({<type={'Order'},Year,Quarter,Month,Week, OrderDate = {"$(='>=' & Date(AddYears(YearStart(Max(OrderDate), 0, 4), -1), 'YourDateFieldFormatHere') & '<=' & Date(AddYears(Max(OrderDate), -1), 'YourDateFieldFormatHere'))"}>} Sales),

If(DateTemp = 'QTD', Sum({<type={'Order'},Year,Quarter,Month,Week, OrderDate = {"$(='>=' & Date(AddYears(QuarterStart(Max(OrderDate)), -1), 'YourDateFieldFormatHere') & '<=' & Date(AddYears(Max(OrderDate), -1), 'YourDateFieldFormatHere'))"}>} Sales),

If(DateTemp = 'MTD', Sum({<type={'Order'},Year,Quarter,Month,Week, OrderDate = {"$(='>=' & Date(AddYears(MonthStart(Max(OrderDate)), -1), 'YourDateFieldFormatHere') & '<=' & Date(AddYears(Max(OrderDate), -1), 'YourDateFieldFormatHere'))"}>} Sales),

If(DateTemp = 'WTD', Sum({<type={'Order'},Year,Quarter,Month,Week, OrderDate = {"$(='>=' & Date(AddYears(WeekStart(Max(OrderDate)), -1), 'YourDateFieldFormatHere') & '<=' & Date(AddYears(Max(OrderDate), -1), 'YourDateFieldFormatHere'))"}>} Sales),

Sum(Sales)))))

[scotly-victor]

That's a epic

You both are really awesome ,Both are done (fiscal YTD,MTD,QTD,WTD) AND  YTD,MTD,QTD,WTD)

Keep supporting  beginner like us ,have great day.

[#10]

Hey Everyone,

It seems like I am trying to do something similar but I cannot figure it out.

I would like to see YTD, QTD, and MTD for sum of sales based on the date field Booked Date in my data set.

I have tried implementing what is done above (and many variations) but it does not seem to work.

Is there some sort of based calc structure for accomplishing the three aforementioned date ranges when using a date field within the data set?

Please let me know as I've been trying for quite a bit now.

[pandiarajan]

Hi sunny ,

     i want to implement this into indian client i hope u know india's fiscal year start from april to march. in this case how can i calculate YTD from april to as on date