19 Replies Latest reply: Dec 16, 2016 4:49 AM by Pandiarajan Radhakrishnan Branched to a new discussion. RSS

    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?

        • Re: YTD,QTD,MTD,WTD IN Qlik Sense
          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

            • Re: YTD,QTD,MTD,WTD IN Qlik Sense
              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

              `

              • Re: YTD,QTD,MTD,WTD IN Qlik Sense
                scotly victor

                Please help since last two days I was trying

                  • Re: YTD,QTD,MTD,WTD IN Qlik Sense
                    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.

                      • Re: YTD,QTD,MTD,WTD IN Qlik Sense
                        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 ?

                          • Re: YTD,QTD,MTD,WTD IN Qlik Sense
                            Sunny Talwar

                            What does wrong output means? Can you elaborate?

                              • Re: YTD,QTD,MTD,WTD IN Qlik Sense
                                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?

                                  • Re: YTD,QTD,MTD,WTD IN Qlik Sense
                                    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))

                                      • Re: YTD,QTD,MTD,WTD IN Qlik Sense
                                        scotly victor

                                        Sorry It didn't work

                                          • Re: YTD,QTD,MTD,WTD IN Qlik Sense
                                            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 stalwar1

                                              • Re: YTD,QTD,MTD,WTD IN Qlik Sense
                                                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)))))

                                                  • Re: YTD,QTD,MTD,WTD IN Qlik Sense
                                                    scotly victor

                                                    Thank you very for you assistance & support  stalwar1 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.

                                                      • Re: YTD,QTD,MTD,WTD IN Qlik Sense
                                                        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.

                                                          • Re: YTD,QTD,MTD,WTD IN Qlik Sense
                                                            scotly victor

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


                                                            FY 2014-2015

                                                            FY 2015-2016

                                                              • Re: YTD,QTD,MTD,WTD IN Qlik Sense
                                                                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)))))

                                            • Re: YTD,QTD,MTD,WTD IN Qlik Sense
                                              Pandiarajan Radhakrishnan

                                              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

                                        • Re: YTD,QTD,MTD,WTD IN Qlik Sense
                                          Mike Smith

                                          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.