Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor

How to use variable in Set Analysis

Good Day,

Not sure what the best approach is to what I am trying to achieve..

I have a variable called vOutliers which is toggled by a button to 0 and 1, which the user can click to exclude outliers from the report.

I then have a variable called vOutlier_Supplier that will return True or False if the sales value is out of the median range of 2%

 

Spoiler
if
(
sum({<supplier={'ROM', 'VAS'}, datum={">=$(vStartDate)<=$(vEndDate)"}>}sales_value) >
Median(TOTAL {<supplier={'ROM', 'VAS'}, datum={">=$(vStartDate)<=$(vEndDate)"}>}sales_value) * 1.02,
'True',
if
(
(Median({<supplier={'ROM', 'VAS'}, datum={">=$(vStartDate)<=$(vEndDate)"}>}sales_value) -
(Median({<supplier={'ROM', 'VAS'}, datum={">=$(vStartDate)<=$(vEndDate)"}>}sales_value) * 0.02) ) >
sum({<supplier={'ROM', 'VAS'}, datum={">=$(vStartDate)<=$(vEndDate)"}>}sales_value),
'True',
'False'
)
)

For the tables and charts I then use these for each measure calculation with zero suppression...

 

 

Spoiler
=if(
$(vOutliers) = 1,
sum({<supplier={'ROM', 'VAS'}, datum={">=$(vStartDate)<=$(vEndDate)"}>}sales_value),

if(
$(vOutlier_Supplier) = 'True',
0,
sum({<supplier={'ROM', 'VAS'}, datum={">=$(vStartDate)<=$(vEndDate)"}>}sales_value)
)
)

So far all is working, what I am struggling with is to do a Count of suppliers where the vOutlier_Supplier = False

 

I have tried if statement as well as set analysis, I guess the problem is the variable is not directly associated with a dimension to use in the formulas... so I am not sure how to construct it, if even possible.

My question is, is there a way to make use of this vOutlier_Supplier variable in functions like count or aggr...

Something like...

Count(distinct {<$(vOutlier_Supplier )={'False'}>} supplier)

Thanks and Regards,

Kanon

 

5 Replies
Highlighted
Valued Contributor II

Re: How to use variable in Set Analysis

I think you're running into an issue because Set Analysis does not compute row by row, so you cannot take the difference between two fields to calculate the condition.

The easy work around is to compute the Outlier flag in the script to have a value for each row

Highlighted
New Contributor III

Re: How to use variable in Set Analysis

This is much easier if you provide some sample data - or an inline statement from which to load. I don't know the exact answer but can easily work it out with some data.

Highlighted
New Contributor

Re: How to use variable in Set Analysis

Apologies for the late response...

I have created a sample app to simulate something I am trying to achieve...

The two kpi's top left is the ones I am struggling with, to count the outliers ect... if the "Exclude Outliers" button is clicked, the number of orders should reflect this.

The problem I see is to do this in script will not make it dynamic, as the median value can change based on your date selection.

Highlighted
Partner
Partner

Re: How to use variable in Set Analysis

Isn't it working to aet an aggr around your expression and sum all false?

sum(aggr(

if
(
sum({<supplier={'ROM', 'VAS'}, datum={">=$(vStartDate)<=$(vEndDate)"}>}sales_value) >
Median(TOTAL {<supplier={'ROM', 'VAS'}, datum={">=$(vStartDate)<=$(vEndDate)"}>}sales_value) * 1.02,
0,
if
(
(Median({<supplier={'ROM', 'VAS'}, datum={">=$(vStartDate)<=$(vEndDate)"}>}sales_value) -
(Median({<supplier={'ROM', 'VAS'}, datum={">=$(vStartDate)<=$(vEndDate)"}>}sales_value) * 0.02) ) >
sum({<supplier={'ROM', 'VAS'}, datum={">=$(vStartDate)<=$(vEndDate)"}>}sales_value),
'0
'1
)
),supplier))   // or better supplierid if there is something similar

Highlighted
New Contributor

Re: How to use variable in Set Analysis

Good Day,

I have tried the Sum(Aggr... and the value returned is 0. Unfortunately it is not working.... yet 🙂

Spoiler
=sum(aggr(if
(
Sum({<order_date={">=$(vStartDate)<=$(vEndDate)"}>}sales_value) > $(vMedian_Pos),
1,
if
(
$(vMedian_Neg) >
Sum({<order_date={">=$(vStartDate)<=$(vEndDate)"}>}sales_value),
1,
0
)
) supplier, order_date ))

Think I am overthinking this by now... hehe

Thanks though..