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

    Restrict Date Dimension

    Rohit Kumar

      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
            Alluraiah 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
                Rohit Kumar

                Hi,

                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
                    Alluraiah Allu

                    Hi

                     

                    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)

                     

                     

                     

                     

                    or

                     

                     

                     

                     

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




                      • Re: Restrict Date Dimension
                        Rohit Kumar

                        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