Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
ishika00730
Contributor III
Contributor III

Ignore Selection using a Variable

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

9 Replies
sasikanth
Master
Master

HI,

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

can you please elaborate a bit more ?

ishika00730
Contributor III
Contributor III
Author

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

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
Contributor III
Contributor III
Author

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

shiveshsingh
Master
Master

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

qliksus
Specialist II
Specialist II

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
Specialist III
Specialist III

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
Contributor III
Contributor III
Author

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

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