How to use variable in Set Analysis

[kanonkop]

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

[dwforest]

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

[andrew_smith200]

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.

[kanonkop]

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.

[martinpohl]

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

[kanonkop]

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