topic Substracting two dates in set expression in QlikView

Substracting two dates in set expression

SRG — Wed, 25 Nov 2020 16:16:04 GMT

HI Experts,

I am trying to substract two dates and compare the difference to another file and my expression is as below and its not working. The dimension I am using is AsOfDatefiled.

And my expression is

sum({<DateFieldDiff = {"=(AsOfDateField -AsOfDateFieldPMFD)"}>}DataTypeCounter).

Any help would be highly appreciated.

Re: Substracting two dates in set expression

kuba_michalik — Tue, 18 Jun 2019 18:10:49 GMT

Searches starting with = only care if the value returned is truthy or falsy, so your set expression as written will select all values in DateFieldDiff for which AsOfDateField - AsOfDateFieldPMFD in not null and not zero.

I imagine what you want is this:
sum({<DateFieldDiff = {"=DateFieldDiff =(AsOfDateField - AsOfDateFieldPMFD)"}>}DataTypeCounter)
but hard to say if it will work without knowing the structure of your data model.

Re: Substracting two dates in set expression

SRG — Tue, 18 Jun 2019 18:23:36 GMT

Hi,

What I am trying to do is calculate the number of days between AsOfDateField and AsOfDateFieldPMFD and compare that number against the DateFieldDiff .

For example if difference between the two date fields above is 32, I am trying to achieve the below

sum({<DateFieldDiff = {32}>}DataTypeCounter).

How ever I want to calculate the days between them dynamically.

Re: Substracting two dates in set expression

kuba_michalik — Tue, 18 Jun 2019 18:29:13 GMT

Then in general I think my formula should work, but it still depends on how the three fields (DateFieldDiff, AsOfDateField, AsOfDateFieldPMFD) are connected in the data.

If they are in the same table it will work, if AsOfDateField and AsOfDateFieldPMFD are on a separate data island and you make selections on them it will work, if it's something different, then It Depends (tm). Try the formula from my previous post and see if it gives you the result you want.