Syntax Help

[#1]

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...

[greg-anderson]

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

[MayilVahanan]

HI

Try like this

Sum ({<Month={">=$(=MonthName(AddMonths(Today(),-12)))"}>}CountAppt)

[Anonymous]

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...

[Gysbert_Wassenaar]

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)

[#6]

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?

[greg-anderson]

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

[#8]

Ahh... I never defined the top end of the date range!

Thank you for your help!

[greg-anderson]

Exactly.  Glad to help!

[jagan · Accepted solution]

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.