Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bawilson485
New 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

Re: Trouble with using date variables in set analysis

Try this:

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

View solution in original post

10 Replies
aarkay29
Valued Contributor

Re: Trouble with using date variables in set analysis

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

mov
Esteemed Contributor III

Re: Trouble with using date variables in set analysis

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.

bawilson485
New Contributor II

Re: Trouble with using date variables in set analysis

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.

bawilson485
New Contributor II

Re: Trouble with using date variables in set analysis

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
Valued Contributor

Re: Trouble with using date variables in set analysis

If that is true then try this:

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

mov
Esteemed Contributor III

Re: Trouble with using date variables in set analysis

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

Re: Trouble with using date variables in set analysis

Try this:

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

View solution in original post

bawilson485
New Contributor II

Re: Trouble with using date variables in set analysis

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

bawilson485
New Contributor II

Re: Trouble with using date variables in set analysis

This works, thank you!

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