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?

[Gysbert_Wassenaar]

Your QTD and MTD sub-expressions are the same. Both look at the max month.

Other problems depend on what your kind of values you Quarter, Month and OrderDate fields contain. If they contain numbers without formatting then the expression should work. If OrderDate contains values with a date format then you'll need to use the same date format in the set analysis. For example if the date format of OrderDate is DD/MM/YYYY:

=if(DateTemp='YTD',sum({<type={'Order'},Year={$(=Max(Year))},Quarter=,Month=,Week=,OrderDate={"<=$(=Date(Max(OrderDate),'DD/MM/YYYY'))"}>}Sales), ...etc

[scotly-victor]

Thank you very much for your response.

Yes I am able to see my QTD and MTD expressions were wrong .

  Can i get Same expression for  QTD,MTD AND WTD.

Thank you in Advance  gwassenaar‌

`

[scotly-victor]

Please help since last two days I was trying

[sunny_talwar · Accepted solution]

May be this:

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

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

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

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

Sum(Sales)))))

Not sure what you want for the final false statement, but YTD, QTD, MTD and WTD should work. Just remember to replace YourDateFieldFormatHere with your actual OrderDate Format.

[Gysbert_Wassenaar]

Can you post a small qlikview document that demonstrates the problem? See this document for more information: Preparing examples for Upload - Reduction and Data Scrambling

[scotly-victor]

Thank you very much sunny

This code works in my case.

I have tried for  previous ytd , mtd ,qtd,wtd

below is my code

If(DateTemp1 = 'YTD', Sum({<type={'Order'},Year,Quarter,Month,Week, OrderDate = {"$(='>=' & Date(YearStart(Max(OrderDate),-1), 'DD/MM/YYYY') & '<=' & Date(Max(OrderDate)-1, 'DD/MM/YYYY'))"}>} Sales),

If(DateTemp1 = 'QTD', Sum({<type={'Order'},Year,Quarter,Month,Week, OrderDate = {"$(='>=' & Date(QuarterStart(Max(OrderDate),-4), 'DD/MM/YYYY') & '<=' & Date(Max(OrderDate)-1, 'DD/MM/YYYY'))"}>} Sales),

If(DateTemp1 = 'MTD', Sum({<type={'Order'},Year,Quarter,Month,Week, OrderDate = {"$(='>=' & Date(MonthStart(Max(OrderDate),-12), 'DD/MM/YYYY') & '<=' & Date(Max(OrderDate)-1, 'DD/MM/YYYY'))"}>} Sales),

If(DateTemp1 = 'WTD', Sum({<type={'Order'},Year,Quarter,Month,Week, OrderDate = {"$(='>=' & Date(WeekStart(Max(OrderDate),-52), 'DD/MM/YYYY') & '<=' & Date(Max(OrderDate)-1, 'DD/MM/YYYY'))"}>} Sales),

Sum(Sales)))))

I am getting wrong output .

Can you please help me ?

[sunny_talwar]

What does wrong output means? Can you elaborate?

[scotly-victor]

I have created KPI for currrent Ytd ,mtd,qtd,wtd and previous ytd,mtd,qtd,wtd to compare both year sales in Qlik sense.

However Current year output is right ,   I am  wondering Previous year output could be wrong.

Is that code correct which I given above for previous ytd,mtd,qtd,wtd?

[Gysbert_Wassenaar]

Instead of Date(Max(OrderDate)-1 which is only one day earlier then Date(Max(OrderDate) try subtracting a year: Date(AddYears(Max(OrderDate),-1))