Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
scotly-victor
Creator II
Creator II

YTD,QTD,MTD,WTD IN Qlik Sense

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?

1 Solution

Accepted Solutions
sunny_talwar

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.

View solution in original post

19 Replies
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


talk is cheap, supply exceeds demand
scotly-victor
Creator II
Creator II
Author

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
Creator II
Creator II
Author

Please help since last two days I was trying

sunny_talwar

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.

View solution in original post

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


talk is cheap, supply exceeds demand
scotly-victor
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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


talk is cheap, supply exceeds demand