topic Re: problem with variable in QlikView

problem with variable

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

Hi,

I am trying to calculate the amount of ID that entered in Source "online" in the last 90 days from the maximum date.

For example, In the example attached,

if the user selects in the month filter - October.

so the maximum date will be - '2017-10-19 15:00:00'

The last 90 days will be- '2017-07-22 15:00:00'

So the calculation will be - the amount of ID's that entered online from 2017-10-19 15:00:00' till '2017-07-22 15:00:00'

I tried the following calculation:

=count({<source=,source={"Online"},SaleDatenum={">=$((Vlast90days))<=$((VmaxDate))"}>}ID)

I know I can add the dimension Month, like this:

=count({<SaleDateMonth=, source=,source={"Online"},SaleDatenum={">=$((Vlast90days))<=$((VmaxDate))"}>}ID)

And the problem will be solved, but is there another way? Like add $ or 1 .... I do not really know what they're doing

Re: problem with variable

sunny_talwar — Wed, 06 Dec 2017 13:59:46 GMT

I think what you need is this

=Count({<SaleDateMonth, source = {"Online"}, SaleDatenum = {">=$(=Num(Vlast90days))<=$(=Num(VmaxDate))"}>} ID)

or this

=Count({1<source = {"Online"}, SaleDatenum = {">=$(=Num(Vlast90days))<=$(=Num(VmaxDate))"}>} ID)

Re: problem with variable

avkeep01 — Wed, 06 Dec 2017 13:59:49 GMT

Hi Tal Tal,

Using SaleDateMonth= equals ignoring the selection on that field. So when you don't add SaleDateMonth= you're datarange is limited by the dates linked to the selection of SaleDateMonth. That's why you'll need to add SaleDateMonth. Btw same for SaleDateYear.

There are otherways to ignore the SaleDateMonth selection by using alternate states and pull the selection from one alternate state into another one. But that is unnecessarily complicated. So no need for that.

Btw Source= and Source={"Online"} is a bit double. The first one ignores the selection on Source and the second one ignores the selection and sets it to online.

Re: problem with variable

ttal7878 — Wed, 06 Dec 2017 14:30:27 GMT

Thank you!

Re: problem with variable

ttal7878 — Wed, 06 Dec 2017 15:00:33 GMT

Thanks,

But when you add 1 - it means it will ignore all selected dimensions.

If for example in my example there is another dimension,"City" and the user will select a city - the result will not be affected by the measure.

But this is not my intention, I want the result not to be affected only by the source dimension and of course the date

Re: problem with variable

sunny_talwar — Wed, 06 Dec 2017 15:08:33 GMT

In that case use this

=Count({<SaleDateMonth, source = {"Online"}, SaleDatenum = {">=$(=Num(Vlast90days))<=$(=Num(VmaxDate))"}>} ID)

Re: problem with variable

ttal7878 — Wed, 06 Dec 2017 15:18:31 GMT

OK ... I wanted to avoid this... but if there is no other choice then okay

Re: problem with variable

sunny_talwar — Wed, 06 Dec 2017 15:22:46 GMT

Based on what you require, this is the only way... not sure I understand the reason to avoid this