Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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()

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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)




View solution in original post

5 Replies
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


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author


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 :

Not applicable
Author

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

Anonymous
Not applicable
Author

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)




Not applicable
Author

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