Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

scotly-victor
Contributor

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

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.

19 Replies

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
scotly-victor
Contributor

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

`

scotly-victor
Contributor

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

Please help since last two days I was trying

MVP
MVP

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.

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
scotly-victor
Contributor

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 ?

MVP
MVP

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

What does wrong output means? Can you elaborate?

scotly-victor
Contributor

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?

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