Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

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

Try this:

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

View solution in original post

10 Replies
Specialist
Specialist

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

Champion III
Champion III

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.

Contributor II
Contributor II

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.

Contributor II
Contributor II

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?

Specialist
Specialist

If that is true then try this:

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

Champion III
Champion III

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');

Try this:

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

View solution in original post

Contributor II
Contributor II

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])

Contributor II
Contributor II

This works, thank you!

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