Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Trouble with using date variables in set analysis

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

1 Solution

Accepted Solutions
sunny_talwar

Try this:

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

View solution in original post

10 Replies
aarkay29
Specialist
Specialist

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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

If that is true then try this:

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

Anonymous
Not applicable
Author

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

Try this:

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

Anonymous
Not applicable
Author

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
Not applicable
Author

This works, thank you!

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