Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Syntax Help

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

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Syntax 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.

View solution in original post

9 Replies
Highlighted
Luminary
Luminary

Re: Syntax Help

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

Highlighted

Re: Syntax Help

HI

Try like this

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

Highlighted
Specialist
Specialist

Re: Syntax Help

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: Syntax Help

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)


talk is cheap, supply exceeds demand
Highlighted
Not applicable

Re: Syntax Help

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?

Highlighted
Luminary
Luminary

Re: Syntax Help

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

Highlighted
Not applicable

Re: Syntax Help

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

Thank you for your help!

Highlighted
Luminary
Luminary

Re: Syntax Help

Exactly.  Glad to help!

Highlighted
MVP & Luminary
MVP & Luminary

Re: Syntax 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.

View solution in original post