Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
alanmcgrath
Contributor

QlikSense variable in set analysis

Hi All,

I'm having trouble with an expression in QlikSense.  This expression works fine in QlikView but when trying to use in QlikSense it is not working.  I've reviewed several posts in the community but still can't seem to solve the issue.  Any help is appreciated.

I set a date using a variable - vMaxDate = Max(ReportDate)

Then I use the following expression:  Sum({<ReportDate = {'$(vMaxDate)'}>}Hours). (works in QV but not QS)

I tested the variable in a KPI object and am getting the correct date (10/31/2018).  However the expression shows 0.  Interestingly, when I change the variable to a literal...vMaxDate = '10/31/2018', the expression works fine.  But I can't seem to get the date variable to work.  I've tried the following:

Sum({<ReportDate = {'$(vMaxDate)'}>}Hours)

Sum({<ReportDate = {"$(vMaxDate)"}>}Hours)

Sum({<ReportDate = {$(vMaxDate)}>}Hours)

Sum({<ReportDate = {vMaxDate}>}Hours)

Any Suggestions?

I have added a screen shot showing the issue.  

1 Solution

Accepted Solutions

Re: QlikSense variable in set analysis

Can you try one of these

Sum({<ReportDate = {"$(=Date($(vMaxDate)))"}>}Hours)

or

Sum({<ReportDate = {"$(=Date(vMaxDate))"}>}Hours)
6 Replies
muthukumar77
Contributor III

Re: QlikSense variable in set analysis

Hi,

1. Check date format of that date column.

2.  use like this Sum({<ReportDate = {"=$(vMaxDate)"}>}Hours) or Sum({<ReportDate = {"$(=vMaxDate)"}>}Hours)

 

Thanks,

Muthukumar

 

Muthukumar Pandiyan
MVP
MVP

Re: QlikSense variable in set analysis

I would adjust the vMaxDate to this:

vMaxDate = Date(Max(ReportDate)) 

Since Max() always only return the numeric part of the maximum date - no formatting - no dual value. So you will lose the formatting and have to re-establish that since set expressions with date field that is dual will demand that the right-hand-side also have the dates as duals or string.

This should then work:

Sum( {ReportDate={'$(vMaxDate)'}>} Hours)

alanmcgrath
Contributor

Re: QlikSense variable in set analysis

Date format looks ok.  When viewing in KPI object it shows 10/31/2018.  Unfortunately, the change suggested didn't work. Still showing 0. 

MVP
MVP

Re: QlikSense variable in set analysis

Just inspect what kind of value the vMaxDate returns to see if it is a number date or a formatted date by displaying it in a text object... It really seems that there is a conflicting dual vs unformatted non-dual number date issue that you are having...

Re: QlikSense variable in set analysis

Can you try one of these

Sum({<ReportDate = {"$(=Date($(vMaxDate)))"}>}Hours)

or

Sum({<ReportDate = {"$(=Date(vMaxDate))"}>}Hours)
alanmcgrath
Contributor

Re: QlikSense variable in set analysis

 The first one did it!  So I guess it was a format issue with the ReportDate field.  Strange because it showed correctly when checking it in a Text and KPI object.  Glad it is working now.

Thanks so much!