11 Replies Latest reply: Aug 26, 2016 9:31 AM by Ishan Kumar RSS

    YTD In qlik sense

    Ishan Kumar

      i am working on YTD calculation and i have week and year as a parameter.

      Sum({1 <WEEK={">}WEEK))<=$(=Max({<Year={$(=MAX(Year))}>}WEEK))"}, Year = {$(=MAX(Year))}>}M_COUNT_WEEK)

      when i am using the above query its always fetching me the max of year. ideally it should give me the data for the year that i am selecting. i have 2 years 2016 and 2015 when i am selecting 2015 it still shows me 2016 data. please help.

        • Re: YTD In qlik sense
          Alluraiah Allu

          Hi

           

          First of all  , I do not understand the  selection you made . Seems to be some error

           

           

           

           

          1)Please check highlighted part .

           

          Sum({1 <WEEK={">}WEEK))<=$(=Max({<Year={$(=MAX(Year))}>}WEEK))"},

          Year = {$(=MAX(Year))}>}  M_COUNT_WEEK)

           

          2)

           

          you have declared   Year = {$(=MAX(Year))  ,  AND  '1' in the expression due to this i think year always slects MAX year i.e 2016 !!?

           

           

          Sum({1 <WEEK={">}WEEK))<=$(=Max({<Year={$(=MAX(Year))}>}WEEK))"},

          Year = {$(=MAX(Year))}>}  M_COUNT_WEEK)

          • Re: YTD In qlik sense
            MARCO HADIYANTO

            Hi

             

            Sum({<WEEK={">=$(=Max({<Year={$(=MAX(Year)-1)}>}WEEK))<=$(=Max({<Year={$(=MAX(Year))}>}WEEK))"}, Year = {$(=MAX(Year))}>}M_COUNT_WEEK)

            try this.


            Regards,

              • Re: YTD In qlik sense
                Ishan Kumar

                Thanks Marco.

                But this is giving me exact value for that week. but i need ytd value.

                • Re: YTD In qlik sense
                  Ishan Kumar

                  Hi Marco

                   

                  If i have 3 years then will this condition work?

                  Sum({<WEEK={">=$(=Max({<Year={$(=MAX(Year)-1)}>}WEEK))<=$(=Max({<Year={$(=MAX(Year))}>}WEEK))"}, Year = {$(=MAX(Year))}>}M_COUNT_WEEK)

                    • Re: YTD In qlik sense
                      Jonathan Dienst

                      This expression should work:

                       

                      Sum({<WEEK = {"<=$(=Max({<Year={$(=MAX(Year))}>} WEEK))"}, Year = {$(=MAX(Year))}>} M_COUNT_WEEK)

                       

                      If that is not working, you might want to post a small sample qvf file for more detailed understanding of your model and more accurate help.

                       

                      And yes, this should work no matter how many years you have. If you want to compare the latest (or selected) year with the previous year:


                      Current/selected year

                      Sum({<WEEK = {"<=$(=Max({<Year={$(=MAX(Year))}>} WEEK))"}, Year = {$(=MAX(Year))}>} M_COUNT_WEEK)


                      Prior year (to same week number as current/selected year):

                      Sum({<WEEK = {"<=$(=Max({<Year={$(=MAX(Year))}>} WEEK))"}, Year = {$(=MAX(Year)-1)}>} M_COUNT_WEEK)