Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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
galax_allu
Valued Contributor

Re: Restrict Date Dimension

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)




5 Replies

Re: Restrict Date Dimension

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

galax_allu
Valued Contributor

Re: Restrict Date Dimension


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

Re: Restrict Date Dimension

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

galax_allu
Valued Contributor

Re: Restrict Date Dimension

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

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