topic Re: Ignore Selection using a Variable in QlikView

Ignore Selection using a Variable

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

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

Re: Ignore Selection using a Variable

sasikanth — Thu, 14 Jun 2018 07:15:38 GMT

HI,

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

can you please elaborate a bit more ?

Re: Ignore Selection using a Variable

ishika00730 — Thu, 14 Jun 2018 07:31:35 GMT

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

Re: Ignore Selection using a Variable

sasikanth — Thu, 14 Jun 2018 07:45:35 GMT

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??

Re: Ignore Selection using a Variable

ishika00730 — Thu, 14 Jun 2018 08:30:28 GMT

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

Re: Ignore Selection using a Variable

shiveshsingh — Thu, 14 Jun 2018 08:43:40 GMT

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

Re: Ignore Selection using a Variable

qliksus — Thu, 14 Jun 2018 08:58:36 GMT

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])

Re: Ignore Selection using a Variable

mdmukramali — Thu, 14 Jun 2018 09:00:41 GMT

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

Re: Ignore Selection using a Variable

ishika00730 — Thu, 14 Jun 2018 09:26:30 GMT

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

Re: Ignore Selection using a Variable

sasikanth — Thu, 14 Jun 2018 12:53:11 GMT

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