Ignore Selection using a Variable

[ishika00730]

Hi,

I have created 2 variables, one is for 'From Date' and Other is for 'To Date'.

vFrom Date-- =MakeDate(vFromYear,vFromMonth,1)

vToDate-- =MakeDate(vToYear,vToMonth,1)

Now I have an expression and I want to make that expression independent of this variable. Meaning any change in From Date should not affect my expression.

My expression looks like below.

=Count(DISTINCT {(<[Status] = {"P"},Join_Date={"<=$(=vToDate)"},=>)} [Customer ID])

Now whenever there is a change in from date my expression changes. Also if I use '1' in the expression,then it stops responding to any selection.

Please suggest how to use this variable in my set analysis.

Many Thanks

Ishika

[sasikanth]

HI,

Your expression doesnot even contains From Date ,how come this effect your exp??

can you please elaborate a bit more ?

[ishika00730]

Hi,

Even if my expression does not contain From date does not mean that it will not have an influence.

All the selections that you make in your dashboard will affect your count.

Can you give me an example of how to write an expression in set analysis to exclude a variable. You may disregard my expression above and suggest a new expression with my requirement. That will be a help.

Best Regards

Ishika

[sasikanth]

HI,

If it as field then should effect your Aggregations i.e expressions,

if it is a variable until unless you use it in your exp it should not effect.

can you please share your file if possible??

[ishika00730]

But vFromDate and vTodate are derived from fields and it is affecting.

[shiveshsingh]

If they are derived from fields, then you can bypass the fields in your set expression.

[qliksus]

I assume you have a Year, Month field you use to populate the variable VMonth, Vyear  . If so the below should be fine

Count(DISTINCT {(<[Status] = {"P"},Join_Date={"<=$(=vToDate)"},Year=,Month=>)} [Customer ID])

[mdmukramali]

Hi,

Can you cross check your expression syntax:

=Count(DISTINCT {(<[Status] = {"P"},Join_Date={"<=$(=vToDate)"},=>)} [Customer ID])

Like :

=Count( {<[Status] = {"P"},Join_Date={"<=$(=vToDate)"}>)} DISTINCT [Customer ID])

And cross check one more othing :

When you select vFromDate and vToDate

On which Date field you are applying this selection criteria

?

[ishika00730]

I have a Order Date Field from which I am deriving From Date and To Date. So I cannot bypass this date field in my set expression.

I need to bypass the vFromDate Variable.

The logic is to select the order date ex from 01-01-2018~31-05-2018. Now I have created a variable vFromDate which populates the start date and then vFromDate that populates the end date and based on this, The order date is selected and I get the necessary output.

To count the number of customers, I want that I should have the count of all the members who have order date until 31st May irrespective of the date getting selected as 01-01-2018~31-05-2018.

So this basically tells me how many members do I have until 31-0-2018 ignoring the from date.

Regards

Ishika

[sasikanth]

HI Ishika,

correct me if am wrong,

you might want to calculate No. of customers till vToDate i.e all the customers before vToDate and bypass FromDate change selection in OrderDate.

Create a variable to store Fromdate value like below and use in expression.

vFromtest

=min({1}OrderDate)      // it doesn't change for any selection.

Expression:

=Count(DISTINCT {(<[Status] = {"P"},Join_Date={">=$(=vFromTest)  <=$(=vToDate)"}>)} [Customer ID])

try and let me know

Thanks,

Sasi