topic Re: Not IsNull() and Diff between Dates in Set Analysis in QlikView

Not IsNull() and Diff between Dates in Set Analysis

ellenblackwell — Fri, 06 Jul 2018 04:16:03 GMT

I should know this in my sleep. Problem is, I should be asleep, so I'm really struggling. Would appreciate any assistance! I need to translate this expression into set analysis:

If(not isnull([Date_Field1]) and ([Date_Field1]>[Date_Field2]), Sum([Sales])

Basically, I'm summing a value only where two date fields are both populated and one of the dates is greater than the other.

And one more example:

If(Today()-[Date_Field1]<=10, Sum([Sales])

Many thanks!

Re: Not IsNull() and Diff between Dates in Set Analysis

sushil353 — Fri, 06 Jul 2018 04:23:26 GMT

I am assuming you have a dimension in your chart say Dim1

then try this expression:

sum({<Date_Field1 -= {' '}>*<Dim1 = {"[Date_Field1]>[Date_Field2]"}>}[Sales])

HTH

Sushil

Re: Not IsNull() and Diff between Dates in Set Analysis

sunny_talwar — Fri, 06 Jul 2018 13:17:53 GMT

Why don't you create a flag in the script like this

If(Len(Trim(Date_Field1)) > 0 and Date_Field1 > DateField2, 1, 0) as Flag

and then this

Sum({<Flag = {'1'}>}Sales)