5 Replies Latest reply: Jul 19, 2016 1:53 AM by Rohit Kumar RSS

    Restrict Date Dimension

      Hi ,


      I have a Cost measure and Date field , I need to create a Line chart where data will come only for last two month. So the point here is ,I need to restrict date dimension to last 2 months with respect to Maximum date presented in data not by Today()

        • Re: Restrict Date Dimension
          Gysbert Wassenaar

          Have you searched already for solutions to this problem? You're hardly the first with this sort of question: https://community.qlik.com/search.jspa?q=show+last+month. Read this blog post: Relative Calendar Fields

          • Re: Restrict Date Dimension
            Allu Allu

            Hi try this in ' AddCalculated dimension'

            =Sum({<DateField = {">=$(=Date(max(DateField)-60,'MM/DD/YYYY'))<=$(=Date(max(DateField),'MM/DD/YYYY')))"}} Cost)



            *User appropriate date format :

              • Re: Restrict Date Dimension


                My date field is created by below statement


                date(floor(UsageStartDate),'DD/MM/YYYY') as Date_Floor1,


                I have created a calculated measure as


                Sum({<Date_Floor1 = {">=$(=Date(max(Date_Floor1)-60,'DD/MM/YYYY'))<=$(=Date(max(Date_Floor1),'DD/MM/YYYY')))"}} UnBlendedCost)


                and the  drag a line chart , select Date a dimension and above measure as Measure but it is giving me nothing

                  • Re: Restrict Date Dimension
                    Allu Allu



                    try this



                    it is working fine for me


                    check your date formats  i.e


                    whether they are in number or text   ,


                    please check whether the Data actually exists for previous 60 days or 2 months




                    =Sum({<Date_Floor1={'>=$(=Date(ADDMONTHS(max(Date_Floor1),-2),'DD/MM/YYYY'))<=$(=Date(max(Date_Floor1),'DD/MM/YYYY'))'}>} UnBlendedCost)










                    =Sum({<Date_Floor1={'>=$(=Date#(ADDMONTHS(max(Date_Floor1),-2),'DD/MM/YYYY'))<=$(=Date#(max(Date_Floor1),'DD/MM/YYYY'))'}>} UnBlendedCost)

                      • Re: Restrict Date Dimension

                        Hi Mr. Allu,

                        Hope you are doing good!

                        See Initially I was trying to work as

                        set vDate =  max(Date_Floor1 -61);

                        which was giving me correct date as two months back from maximum date in KPI view but when I put "= ="

                        (set vDate = = max(Date_Floor1 -61);) and use this variable in the measure it was giving me 0. thats why I created this Ticket.

                        Few days back when I was using addmonths function to calculate Month Over Month it was giving me 0 when I select two months value in filter. So sometime Addmonth works and sometime Max(date)- days  works.


                        Can you please add your notes on this concern. what exactly I am missing or using wrong