Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
bawilson485
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
Not applicable

Re: Trouble with using date variables in set analysis

Try this:

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

10 Replies
aarkay29
Not applicable

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

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

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

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

Re: Trouble with using date variables in set analysis

If that is true then try this:

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

mov
Not applicable

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

sunny_talwar
Not applicable

Re: Trouble with using date variables in set analysis

Try this:

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

bawilson485
Not applicable

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

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?