4 Replies Latest reply: Sep 20, 2018 3:24 PM by Justin Dallas RSS

    Dates

    Rony Tullus

      Hello,

       

      I've been watching videos on how to work with dates with Qlik. I have successfully formatted my dates as MMM-YY. This field is called Billing Period in the data that I'm working with. When I use a function such as =Max(Billing Period) it correctly shows the max Billing Period in my data. Yet, when I attempt to use set expression it doesn't work. I'm attempting to use the below function:

       

      =sum({$<Date=$(=Max([Billing Period]))>}[Billing Amount])

       

      What I want to ultimately accomplished is to be able to do Month over Month variances and rolling variances (13 months) based on the selection of data. I think first I need to understand why the above function doesn't work. If you also help me understand how to apply the function to do month over month variances and 13 months variances that would be extremely helpful.

       

      Regards,

        • Re: Dates
          vamsee duggirala

          Hi,

          You said your dates are formatted as MMM-YY. This does not have Date in it (DD).

          Try using Makedate() to create date.

           

           

          sum({$<Date={"$(=Max([Billing Period]))"} >}[Billing Amount])

          • Re: Dates
            Rony Tullus

            I tried sum({$<Date={"$(=Max([Billing Period]))"}>}[Billing Amount]). It didn't work for me. Thanks for trying. I formatted my date as MMM-YY in the Data Manger view. I don't know if that makes a difference or not.

              • Re: Dates
                vamsee duggirala

                try adding =Max([Billing Period])) in a text box, date in list box and see if the format is same for both.

                If Yes, select the matching date, add a text box with the following condition and see

                 

                =if(Date=Max([Billing Period],'TRUE', 'FALSE')

                • Re: Dates
                  Justin Dallas

                  Does Qlik know how to compare strings that are formatted like 'YYYY MM'?  In my personal opinion, since I have 'Billing Period', then I can create a date such as 'Start Of Billing Period' from my BillingPeriod.  In that case, I would have a Date object which would have a well defined metric that Qlik could use out of the box.