QlikSense variable in set analysis

[alanmcgrath]

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.  

[muthukumar77]

Hi,

1. Check date format of that date column.

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

Thanks,

Muthukumar

[petter]

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]

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

[petter]

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

[sunny_talwar · Accepted solution]

Can you try one of these

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

or

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

[alanmcgrath]

 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!