problem with variable

[ttal7878]

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

[sunny_talwar · Accepted solution]

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)

[avkeep01]

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.

[ttal7878]

Thank you!

[ttal7878]

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

[sunny_talwar]

In that case use this

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

[ttal7878]

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

[sunny_talwar]

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