Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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?

Tags (4)
1 Solution

Accepted Solutions
Highlighted

Re: YTD,QTD,MTD,WTD IN Qlik Sense

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
Highlighted
MVP & Luminary
MVP & Luminary

Re: YTD,QTD,MTD,WTD IN Qlik Sense

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

Re: YTD,QTD,MTD,WTD IN Qlik Sense

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

`

Highlighted
Creator II
Creator II

Re: YTD,QTD,MTD,WTD IN Qlik Sense

Please help since last two days I was trying

Highlighted

Re: YTD,QTD,MTD,WTD IN Qlik Sense

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: YTD,QTD,MTD,WTD IN Qlik Sense

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

Re: YTD,QTD,MTD,WTD IN Qlik Sense

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 ?

Highlighted

Re: YTD,QTD,MTD,WTD IN Qlik Sense

What does wrong output means? Can you elaborate?

Highlighted
Creator II
Creator II

Re: YTD,QTD,MTD,WTD IN Qlik Sense

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?

Highlighted
MVP & Luminary
MVP & Luminary

Re: YTD,QTD,MTD,WTD IN Qlik Sense

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