Trouble with using date variables in set analysis

[Anonymous]

I am trying to set up some dashboards that will automatically update to current month/year and previous year/month, so i have tried to set up some variables to do those calculations

My current month variable is set up as:

vCurMonth = monthname(addmonths(today(),0))

I used the variable pane under the edit mode for sheets to create this as opposed to doing it in the load script (not sure if this matters)

The formula i am trying to use in a KPI for a monthly total is:

Sum({$<[OINV.DocDate.autoCalendar.YearMonth]={$(vCurMonth)}>} [LineTotal])

But it resolves as "-"

When I put Max([OINV.DocDate.autoCalendar.YearMonth]) in the KPI it evaluates to 2/1/2017 and when I put $(vCurMonth) in the KPI it evaluates to 2/1/2017 too.  So I'm just confused why it won't work.  Many of the discussions i've found say to put quotes around the variable, but that doesn't work either.

Does this not work in Qlik Sense or am i messing up some syntax somewhere? [LineTotal] is in a different, but directly connected, table from OINV, if that matters.

Thanks

[aarkay29]

Can you please check and let us know what is the  format of OINV.DocDate.autoCalendar.YearMonth

[Anonymous]

The monthname() function returns result i a format "Feb 2017".  You must have the same format as you use for the OINV.DocDate.autoCalendar.YearMonth field.

[Anonymous]

How do i check this? Is there a format check function?

I did a logical test in a KPI if($(vCurMonth)=max([OINV.DocDate.autoCalendar.YearMonth]),'TRUE',0) and it returned TRUE, but i would understand if it's a strange formatting issue.

[Anonymous]

I did a logical test in a KPI if($(vCurMonth)=max([OINV.DocDate.autoCalendar.YearMonth]),'TRUE',0) and it returned TRUE, but i would understand if it's a strange formatting issue.

Would replacing the monthname() function with a simple month() or another function solve it?

[aarkay29]

If that is true then try this:

Sum({$<[OINV.DocDate.autoCalendar.YearMonth]={"=$(vCurMonth)"}>} [LineTotal])

[Anonymous]

Formatting the variable properly should solve the problem.  But you need to know first what is the format of your YearMonth field.  You can see it in a list box.  If it is, for example 'M/D/YYYY', try to define your variable a little differently:

LET vCurMonth = date(monthname(addmonths(today(),0)),'M/D/YYYY');

[sunny_talwar · Accepted solution]

Try this:

Sum({$<[OINV.DocDate.autoCalendar.YearMonth]={"=[OINV.DocDate.autoCalendar.YearMonth] = $(vCurMonth)"}>} [LineTotal])

[Anonymous]

When I put the quotation marks within the brackets all text inside the quotes turns green and the output is the same as i would get with Sum([LineTotal])

[Anonymous]

This works, thank you!

Any idea why you have to repeat the field name within the quotes?