12 Replies Latest reply: Feb 11, 2018 4:06 AM by Arvind Kumar Jha RSS

    Dynamic Measure based on Dates

    Arvind Kumar Jha

      Hello Team,

       

      I have a Filter and a Table.

       

      Based on my selection of date in filter i want to know the sales on that particular day and also incremental sales after the selected date in a table

       

      To achieve the same i used greater than format but it doesn't work.

       

      Sum({$<[Last Update Date] = {"> $(=getfieldselections([Last Update Date]))"}>}[Sales])

       

      Kindly help

       

      Thanks,

      Arvind

        • Re: Dynamic Measure based on Dates
          Kaushik Solanki

          Try this.

           

          Sum({$<[Last Update Date] = {">= $(=Max([Last Update Date]))"}>}[Sales])


          If you have selected the year and month fields also then you should remove them as well, like shown below.


          Sum({$<[Last Update Date] = {">= $(=Max([Last Update Date]))"},Month=,Year=>}[Sales])


          Regards,

          Kaushik Solanki

          • Re: Dynamic Measure based on Dates
            Andrea Gigliotti

            your expression looks correct and should give you what you are looking for.

            if you make selections also on month or year field then follow what kaushik.solanki said.

            • Re: Dynamic Measure based on Dates
              Chennaiah Nallani

              try this,

              vFrom = Date(Max(DateField))

              vStart = Date(GetFieldSelections(DateField))

               

              Sum({< Date = {'>=$(=Date(vStart))<=$(=Date(vFrom))'} >} Sales)

              • Re: Dynamic Measure based on Dates
                Arvind Kumar Jha

                Hello Kaushik,

                 

                I dint get your second expression :

                 

                Sum({$<[Last Update Date] = {">= $(=Max([Last Update Date]))"},Month=,Year=>}[Sales])


                How is it useful?


                Can it be explained with example?

                  • Re: Dynamic Measure based on Dates
                    omar bensalem

                    max([Last Update Date]) will return the max of what Qlik is "seing"; I mean when u select nothing, Qlik is seing everything and thus will return u the max.

                     

                    Now Imagine that u select : [Last Update Date] = 05/01/2018 ; now u're forcing qlik to only "see" this values;

                    so the max([Last Update Date]) = max('05/01/2018') = '05/01/2018'


                    Now u want to see the sum of ur measure for the date u're selecting and all the dates that come after it.

                    as we said the max([Last Update Date]) returns the selected date.

                    so to return the >= selected date we need to add >=


                    Now, in ur set expression it would be sthing like this:

                    sum({< [Last Update Date] ={">=$(=max([Last Update Date]))"}>}Sales)


                    if u for for example, want, if u select '05/01/2018' want to see sum Sales from the 05/01/2017 (one year prior) to 05/02/2018 (add 1 month)


                    to return one year: AddYears(max([Last Update Date]),-1) => 05/01/2017

                    to add one Month : AddMonths(max([Last Update Date]),1) =>05/02/2018


                    BUT: if we select 05/01/2018 => The selected date is within the YEAR 2018, so normally, Qlik will only see data relative to YEAR =2018; so to break this, and make Qlik always SEE all the YEARS (to be able to return to the prior one and fetch the sales within it, we use the Key word : Year= Which the same as saying Year={*}

                    Which means, Hey QLIK, I want u to keep seing ALL THE YEARS, even though I've selected a Year, or a date within ONE year.. (Same logic for the Month field)



                    so the expression would become:


                    sum({<Year=,Month=, [Last Update Date] ={">=$(=AddYears(max([Last Update Date]),-1)) <=$(=AddMonths(max([Last Update Date]),1))"}>}Sales)


                    Hope that was clear?

                      • Re: Dynamic Measure based on Dates
                        Arvind Kumar Jha

                        Hello Omar,

                         

                        Thanks for the elaborate explanation.

                         

                        But is there a way to get sum of a measure between random dates selected by a user?

                         

                        Thanks,

                        Arvind

                          • Re: Dynamic Measure based on Dates
                            omar bensalem

                            u'd have a date field; the user select 2 dates from the same field; and u want to return the sales btwn the 2 selected dates?

                             

                            so If ur user select, 15/11/2017 and 11/01/2018 , u want sales btwn those 2 dates right?

                             

                            now, if I have well explained the process to u, u should figure it out by urself

                             

                            if u select these 2 dates:

                            to retrieve the min date btwn the both? =>15/11/2017 => min(Date)

                            to retrieve the max date btwn the both? =>11/01/2018 => max(Date)


                            => sum({<Year=,Month=,Date={">=$(=min(Date)) <=$(=max(Date))"}>}Sales)


                             

                             

                              • Re: Dynamic Measure based on Dates
                                Arvind Kumar Jha

                                Hello Omar,

                                 

                                Thanks a lot for the reply.

                                The above worked ,but the same through YearMonth field doesn't work like below :

                                 

                                sum({<Year=,Month=,[Launch Date]={">$(=Date(min([[Launch Date.autoCalendar.YearMonth]])))

                                <$(=Date(max([[Launch Date.autoCalendar.YearMonth]])))"}>}Sales)

                                 

                                Actually i wanted filter to be as YearMonth

                                 

                                Thanks,

                                Arvind

                                  • Re: Dynamic Measure based on Dates
                                    omar bensalem

                                    Hi Arvind,

                                     

                                    When facing trouble with these expressions; always create text object;

                                    and create a measure containing the parts you're having a doubt about;

                                    I mean : create these measures:

                                    =Date(min([[Launch Date.autoCalendar.YearMonth]]))

                                    =Date(max([[Launch Date.autoCalendar.YearMonth]]))


                                    and see what they're returning; and from there, begin to alter them untill it corresponds to ur need and they return the expected dates in the format of ur Launch Date


                          • Re: Dynamic Measure based on Dates
                            Arvind Kumar Jha

                            Hello Nallani,

                             

                            Can we get sum between two dates which is not maximum or minimum and can be any random value?

                            For example Dates range from Jan 2010 to Jan 2018 ?

                             

                            If i want to see sales between Mar 2013 to Jan 2014 based on filter section, i.e. basically there will be two filters,From date and To date for the same date field

                            • Re: Dynamic Measure based on Dates
                              Arvind Kumar Jha

                              Hello Omar,

                               

                              Actually i had already created these measures and they are returning correct values but not giving correct results :

                               

                              sum({<Year=,Month=,[Launch Date]={">$(=Date(min([[Launch Date.autoCalendar.YearMonth]])))

                              <$(=Date(max([[Launch Date.autoCalendar.YearMonth]])))"}>}Sales)