topic Re: Syntax Help in QlikView

Syntax Help

Thu, 03 Oct 2013 15:08:57 GMT

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

Re: Syntax Help

greg-anderson — Thu, 03 Oct 2013 15:12:01 GMT

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

Re: Syntax Help

MayilVahanan — Thu, 03 Oct 2013 15:12:34 GMT

Try like this

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

Re: Syntax Help

Anonymous — Thu, 03 Oct 2013 15:13:22 GMT

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

Re: Syntax Help

Gysbert_Wassenaar — Thu, 03 Oct 2013 15:17:21 GMT

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)

Re: Syntax Help

Thu, 03 Oct 2013 15:19:06 GMT

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?

Re: Syntax Help

greg-anderson — Thu, 03 Oct 2013 15:26:04 GMT

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

Re: Syntax Help

Thu, 03 Oct 2013 15:27:28 GMT

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

Thank you for your help!

Re: Syntax Help

greg-anderson — Thu, 03 Oct 2013 15:28:12 GMT

Exactly. Glad to help!

Re: Syntax Help

jagan — Thu, 03 Oct 2013 15:28:48 GMT

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.