5 Replies Latest reply: Feb 4, 2016 4:39 AM by Sunny Talwar RSS

    Set Analysis on date greater than variable

      I search some similar questions but the solution doesn't work in my case, please help

       

      LET v6MonthsAgo = Date(Addmonths(Today(),-6));

       

      Below works:

      Sum(if(KPI_DATE>=v6MonthsAgo, KPI_SALES, 0))

       

      Below doesn't work:

      Sum({<KPI_DATE={">=$(v6MonthsAgo)"}>} KPI_SALES)

       

       

      My actual use is Avg so cannot use if (..., ..., ...)

        • Re: Set Analysis on date greater than variable
          Gysbert Wassenaar

          Probably the date format that's giving problems. Try LET v6MonthsAgo = num(Addmonths(Today(),-6));

          If that doesn't work then post a small qlikview document that demonstrates the problem.

            • Re: Set Analysis on date greater than variable

              I try to use Date() and MakeDate() and turn out MakeDate() works but Date() doesn't.  Any idea on this?

               

              Sum({<DATE_FROM_MAKEDATE={">=$(v6MonthsAgo)"}>} VOLUME) <-- works

               

              Sum({<DATE_FROM_STR={">=$(v6MonthsAgo)"}>} VOLUME) <-- not work

               

              LOAD INDEX_DATE,

                  DATE_TRUNC,

                  DATE_YEAR,

                  DATE_MONTH,

                  DATE_DAY,

                  DATE_STR,

                  Date(DATE_STR,'YYYY-MM-DD') as DATE_FROM_STR,

                  MakeDate(DATE_YEAR, DATE_MONTH, DATE_DAY) as DATE_FROM_MAKEDATE,

                  INDEX_VOLUME AS VOLUME;

              SQL SELECT

                  INDEX_DATE as INDEX_DATE,

                  TRUNC(INDEX_DATE) as DATE_TRUNC,

                  EXTRACT(YEAR FROM INDEX_DATE) DATE_YEAR,

                  EXTRACT(MONTH FROM INDEX_DATE) DATE_MONTH,

                  EXTRACT(DAY FROM INDEX_DATE) DATE_DAY,

                  TO_CHAR(INDEX_DATE, 'YYYY-MM-DD') as DATE_STR,

                  INDEX_VOLUME

              FROM INDEX_TABLE;

                • Re: Set Analysis on date greater than variable
                  Sunny Talwar

                  Try this with your the expression you are using currently:

                   

                  LOAD INDEX_DATE,

                      DATE_TRUNC,

                      DATE_YEAR,

                      DATE_MONTH,

                      DATE_DAY,

                      DATE_STR,

                      Date(DATE_STR) as DATE_FROM_STR,

                      MakeDate(DATE_YEAR, DATE_MONTH, DATE_DAY) as DATE_FROM_MAKEDATE,

                      INDEX_VOLUME AS VOLUME;

                  SQL SELECT

                      INDEX_DATE as INDEX_DATE,

                      TRUNC(INDEX_DATE) as DATE_TRUNC,

                      EXTRACT(YEAR FROM INDEX_DATE) DATE_YEAR,

                      EXTRACT(MONTH FROM INDEX_DATE) DATE_MONTH,

                      EXTRACT(DAY FROM INDEX_DATE) DATE_DAY,

                      TO_CHAR(INDEX_DATE, 'YYYY-MM-DD') as DATE_STR,

                      INDEX_VOLUME

                  FROM INDEX_TABLE;

              • Re: Set Analysis on date greater than variable
                jagan mohan rao appala

                HI,

                Try like this

                 

                LET v6MonthsAgo = Date(Addmonths(Today(),-6));

                 

                Sum({<KPI_DATE={">=$(=v6MonthsAgo)"}>} KPI_SALES)

                 

                Note: The date format in v6MonthsAgo & KPI_DATE field should be same then only this works.

                 

                Regards,

                Jagan.

                • Re: Set Analysis on date greater than variable
                  balraj ahlawat

                  try like this?

                   

                  LET v6MonthsAgo = Date(Addmonths(Today(),-6));

                   

                  =Sum({<KPI_DATE=, KPI_DATE={'>=$(=v6MonthsAgo)'}>} KPI_SALES)

                   

                  Make Sure Format of v6MonthsAgo & KPI_DATE should be same like 'DD-MM-YYYY'