9 Replies Latest reply: Oct 17, 2018 5:08 AM by Lisa Valpassos RSS

    Date comparison

    Lisa Valpassos

      Hi all,

       

      I have this formula giving me the variance between two years, making use of Flags as created in my master calendar:

       

      sum({<CYTD={1}>} Sales)

      /

      (sum({<FPYTD={1}>} Sales)) - 1


      YTD = Current Year-to-Date:

      FPYTD: First prior Year-to-Date

       

      And I have a QuarterYear field declared in this way:

       

      dual('Q' & ceil(month(D)/3) & '-' & Year(D),ceil(month(D)/3)+Year(D)*10)

       

      This gives me Q1-2017,Q2-2018,...

       

      PROBLEM: when I filter by QuarterYear, my % variance collapses. I no longer have the denominator, always rendering zero.

       

      Do you know why this is happening?

       

      Thanks,

       

      Lisa

        • Re: Date comparison
          Dilip Ranjith

          Hi Lisa

          Basically the way qlik works when you select QuarterYear Qlik will filter out all the rows for that Quarteryear and then Qlik applies the set analysis.As the data set no longer (from your question) contains any values with FPYTD=1 it returns 0

          if you want to ignore the selection add QuarterYear= into your set analysis

           

          Hope it helps

            • Re: Date comparison
              Lisa Valpassos

              Hi Dilip,

               

              Thanks for your input!

              But the thing is, I don't want to ignore QuarterYear. If I select Q1-2018, I want the expression to compute the sales value for the first quarter in 2018, divided by the first quarter in 2017.

               

              What do I need to do to make this happen?

               

              Thanks!

               

              Lisa

                • Re: Date comparison
                  raman RASTOGI

                  Hi Lisa

                   

                  Try this

                  Make a field of quarter in your master calendar

                  ex- Ceil(month(D)/3 as Quarter

                   

                  And use below expression

                  =Sum({$<Year={"$(=max(Year))"},  Quarter= {"$(=max(Quarter))"}>}SALES) /

                  Sum({$<Year={"$(=max(Year)-1)"}, Quarter= {"$(=max(Quarter))"}>}SALES)


                  Raman

                    • Re: Date comparison
                      Lisa Valpassos

                      Hi Raman!

                       

                      So, you don't recommend using flags like CYTD?

                       

                      I created the field Ceil(Month(D)/3 in my script, and now I have a filter Quarter that displays Q1,Q2,Q3,Q4. But the ideal would be to have the field QuarterYear as in Q1-2018 throughout my app, so I don't lose context of the year.

                       

                      Is this incompatible with using flags like CYTD?

                       

                      Thanks!

                       

                      Lisa

                        • Re: Date comparison
                          Dilip Ranjith

                          Flags are a good practice but if you want it to react to year selections you will need to do somethings like raman mentioned.

                          Alternatively in your original you can design the app to just select quarter instead of quarter year

                          • Re: Date comparison
                            raman RASTOGI

                            Hi Lisa

                             

                            As dilip said using of flags is consider as a good practices but here you want your values based on selections so i suggested use of direct fields instead of Flags.

                             

                            QuarterYear is a string value which can not be use to compare values in set analysis. so i used separate quarter filed.


                            Raman

                              • Re: Date comparison
                                Lisa Valpassos

                                Hi raman.rastogi and hi dilipranjith!

                                 

                                My master calendar is based on flags... Mostly all of it.

                                Under which circumstances is then best to use flags? Because it's normal to expect my KPIs reacting to filter selections.

                                 

                                Thanks!

                                 

                                Lisa

                                  • Re: Date comparison
                                    Dilip Ranjith

                                    Hi Lisa

                                    Using flags (in the setanalysis) will work

                                    - if always comparing current year to previous (any dimension quarter or daily).

                                         - but this will be affected if you select a filter with year there. like i mentioned in my first reply

                                         - typically you would ignore those filters in the set analysis e.g. Quarter Year, year etc. but other filters like quarted

                                     

                                    If you need get the chart to be truly dynamic i.e. if you choose a year in the filter and chart will show that year and prior year. you will need to use variables. something like below

                                     

                                    e.g. comparing between selected date and revious 6 months

                                    declare 2 variables

                                    vTestDate - =if(GetSelectedCount(OrderDate)=1,OrderDate,0)

                                    vTestDateMinus6   -   =if(GetSelectedCount(OrderDate)=1,Date(AddMonths(OrderDate,-6)),0)

                                     

                                    your expression

                                    if(vTestDate=0, Sum( OrderRecordCounter),Sum( {<OrderDate={"<=$(=vTestDate) >=$(=vTestDateMinus6)"}>} OrderRecordCounter))

                                    • Re: Date comparison
                                      raman RASTOGI

                                      Hi lisa

                                       

                                      * We generally use flags to reduce calculation time from front end to increase charts response time.

                                      It is good to use when you have complex business logic.

                                       

                                      Use of flags give you less flexibility 


                                      ex - As you made CYTD flag in this only current year will come under this flag and if you want to change your year based on selection you cant change your flag value on selection.

                                      So in this case you have to write whole logic in your expressions.

                                       

                                       

                                       

                                      Thanks

                                      Raman