Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a chart with the expression....
Sum ({<Month={">=$(=AddMonths(Today(),-12))"}>}CountAppt)
Month is in 'YYYY-MMM' format. I want to see the last 12 complete months in the chart. So if we are in October 2013 I want to see September 2013->October 2012 going back in time.
I can't figure out why I keep getting 'No data to display' and I am sure it is something simple that I am overlooking...
Hi,
Please try below expression, if you have Date dimension.
Sum ({<DateDimension={">=$(=AddMonths(Today(),-12))<=$(=Today())"}>}CountAppt)
If not works then check the date format DateDimension and Today() are same if not then change Date(Today(), 'DateFormat').
Regards,
Jagan.
First guess- QlikView is seeing your 'Month' field as text. You need to make sure it sees the field as a date (e.g., use '2013-10-01' rather than '2013-10' so it will guess, or use an explicit conversion.
Hope it helps.
-Greg
HI
Try like this
Sum ({<Month={">=$(=MonthName(AddMonths(Today(),-12)))"}>}CountAppt)
try something like this in the SET...
Sum ({<Month={">=$(=Year(AddMonths(Today(),-12)) & '-' & Upper(Left(MonthName(AddMonths(Today(),-12)), 3)))"}>}CountAppt)
better yet..
create a variable out of that
and use that variable in the set...
AddMonths(Today(),-12) calculates a date. So your Month field has to be a date field too. If your Month field is a string then >= comparison won't work either. In that case create a Month field that is a date formatted as YYYY-MMM:
date(monthstart(MyDateFieldHere),'YYYY-MMM') as Month. And you'll probably have to use the date function to format the result of addmonths too: Sum ({<Month={">=$(=date(AddMonths(Today(),-12),'YYYY-MMM'))"}>}CountAppt)
Thank you Aadil
Using your expresion was able to limit 12 months prior but I still have future dates in the graph. I am seeing dates through 2014. Any thoughts?
Your statement currently only limits the month to anything greater than 12 months prior. You need to add a condition in the expression to limit month to anything between 12 months prior and today.
You can do this with a BETWEEN clause or by using Sum ({<Month={">=$(=Year(AddMonths(Today(),-12)) & '-' & Upper(Left(MonthName(AddMonths(Today(),-12)), 3)))"}> AND <Month={"<=$(=Year(Today(),-12)) & '-' & Upper(Left(MonthName(Today()), 3)))"}>}CountAppt).
-Greg
Ahh... I never defined the top end of the date range!
Thank you for your help!
Exactly. Glad to help!
Hi,
Please try below expression, if you have Date dimension.
Sum ({<DateDimension={">=$(=AddMonths(Today(),-12))<=$(=Today())"}>}CountAppt)
If not works then check the date format DateDimension and Today() are same if not then change Date(Today(), 'DateFormat').
Regards,
Jagan.