topic Re: Set Analysis and If bringing back different values in QlikView

Set Analysis and If bringing back different values

Fri, 21 Aug 2015 19:11:58 GMT

I have a situation where I have been using the expression =Sum({$} if(AssAssignmentDate <= Date(Today()), AssCharge)) and I wanted to look at changing this to a Set Analysis throughout my document to gain better performance. I have the formula =Sum({$} if(AssAssignmentDate <= Date(Today()), AssCharge)) in a text box which brings back a value of $134,286,198.50 Next to this I have another text box with the following formula =Sum({$<AssAssignmentDate={"<=$(Date(Today()))"}>}AssCharge) and this brings back a value of $134,291,742.41

I do not have any selections made in my document at all.

I'm really confused as to why these values are different. Any thoughts would be greatly appreciated.

Ryan

Re: Set Analysis and If bringing back different values

swuehl — Fri, 21 Aug 2015 20:17:14 GMT

Isn't there an equal sign missing in the dollar sign expansion?

=Sum({$<AssAssignmentDate={"<=$(=Date(Today()))"}>}AssCharge)

Re: Set Analysis and If bringing back different values

Fri, 21 Aug 2015 20:21:25 GMT

I think that is only if you are referencing a variable, i I add an equal sign it comes back with a total of zero instead

Re: Set Analysis and If bringing back different values

swuehl — Fri, 21 Aug 2015 20:32:11 GMT

You'll need the equal sign to evaluate the expression.

If you remove it, I think you'll get the same result as when not using the set analysis filter at all (you can double check this).

If you get zero with an added equal sign, there seems to be an issue with the format of the field vs. format of your expression in the modifier.

What is your default date format and what is the format of field AssAssignmentDate?

Re: Set Analysis and If bringing back different values

Fri, 21 Aug 2015 20:42:25 GMT

Yes you are correct, thank you. If I remove the modifier I get the same result as having the modifier, so the equals is needed

My date was a timestamp but I set to the following in my script adapter - Floor(date(Timestamp(AssignmentDate), 'DD/MM/YYYY')) as AssAssignmentDate

Re: Set Analysis and If bringing back different values

swuehl — Fri, 21 Aug 2015 20:53:00 GMT

That probably removed the date formatting from your field, and there seems to happen some unnecessary formatting.

Have you checked that's not a numeric value without date formatting?

try

=Sum({$<AssAssignmentDate={"<=$(=Num(Today()))"}>}AssCharge)

Re: Set Analysis and If bringing back different values

Fri, 21 Aug 2015 20:55:10 GMT

Perfect, that worked. Thank you so much for your help.