4 Replies Latest reply: Oct 16, 2015 12:01 PM by RajQlikview 11 RSS

    last 6 months - but consider EOM only

    RajQlikview 11

      Hi All,

      Thanks for your time,

      I have the data from April through October but I need to restrict the line chart only for the last six months (do not consider October as it is not yet completed) consider as of last i.e sep EOM only - I have some data for October but not full so need to ignore October


      my dimensions are




      expressions is

      ({<Cost_Year_Month ={">=$(=Date(addmonths(Max(Cost_Year_Month), -5), 'MMM-YY')) <=$(=Date(addmonths(Max(Cost_Year_Month), 1),  'MMM-YY'))"} >} Total_Cost

      but this is giving me October also - I need to do a max - 1



      ** Another Question: for another straight table: I have to use a straight table but only show last 6 months

      If I have Cost_Year_Month as a dimension along with some other dimensions and say expressions is sum(cost)

      is there any easy way of displaying only the last 6 months with these calcs


      Thanks for your time

      Raj T

        • Re: last 6 months - but consider EOM only

          Try the following:

          ({<Cost_Year_Month ={">=$(=Date(addmonths(Max(Cost_Year_Month)-1, -5), 'MMM-YY')) <=$(=Date(addmonths(Max(Cost_Year_Month)-1, 1),  'MMM-YY'))"} >} Total_Cost)

          -- This should work in a straight table as well.

          • Re: last 6 months - but consider EOM only
            Digvijay Singh



            I suggest use your expression in straight table without changing label, it will show the result of your set expression in the header.

            I am not sure why didn't you use -1 in 2nd condition when you don't want to use current month data -

            <=$(=Date(addmonths(Max(Cost_Year_Month), 1),  'MMM-YY'))"} >}

            Also check if date is properly formatted as I suggested above through straight table expression header

            • Re: last 6 months - but consider EOM only
              Jonathan Dienst



              {<Cost_Year_Month ={">=$(=Date(MonthStart(Max(Cost_Year_Month), -5), 'MMM-YY')) <=$(=Date(MonthStart(Max(Cost_Year_Month)) - 1,  'MMM-YY'))"}>}

                • Re: last 6 months - but consider EOM only
                  RajQlikview 11

                  Thank you Jonathan (Thank you All),

                  Actually, I got wrong instructions - user says now he wants to see the October also - i.e last 6 months including the October -


                  (fabs(Sum({<Cost_Year_Month ={">=$(=Date(addmonths(Max(Cost_Year_Month), -5), 'MMM-YY')) <=$(=Date(addmonths(Max(Cost_Year_Month), 1),  'MMM-YY'))"} >} Total_Cost)), '$#,##0')

                  This expression is giving me the data from May to October in the line chart -  but now I have another issue I have to get the

                  data points in the line chart to show K, M,B instead of the full number - used something like below with a new expression and

                  checked values on data points but this is giving me the april data also



                  num(fabs(sum(Cost_Year_Month))/1000000,'#,##0.0 M'),

                  num(fabs(sum(Cost_Year_Month))/1000,'#,##0 K'))


                  Thank you all,