topic Re: Restrict Date Dimension in App Development

Restrict Date Dimension

Fri, 15 Jul 2016 11:12:40 GMT

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 — Fri, 15 Jul 2016 11:16:47 GMT

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

Anonymous — Fri, 15 Jul 2016 11:27:50 GMT

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

Fri, 15 Jul 2016 11:40:32 GMT

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

Anonymous — Fri, 15 Jul 2016 12:15:18 GMT

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

Tue, 19 Jul 2016 05:53:10 GMT

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