topic Re: Filter on Calculated Variable in Set Analysis in App Development

Filter on Calculated Variable in Set Analysis

Yorick_Stevens — Thu, 29 Oct 2020 12:14:59 GMT

Hi All,

I'm constructing a table for our travel team, analyzing the expense cities to where we travel.
One of the calculations i make is suggestion new expensive cities to be considered in our travel policy. (the result of the measure is a 1 or a 0, the variable is called vNewExpensive

Now i would like to filter on these in set analysis for my further calculations.
Currently I have something like this: [DestinationCity] = {"=$(=$(vNewExpensive))<1"} in my set analysis, but it doesn't seem to work

vNewExpensive has the following expression:
if(
//Service Length has to be longer than 20
Sum({< [ReferenceDate.Calendar.DateNum] = {">=$(=$(SelectedDateMinYTM))<$(=$(SelectedDateMaxYTM))"},ExpenseFlag={'0'}>} ServiceLength) >= 20
AND
//# Booking shas to be bigger than 0
Count({< [ReferenceDate.Calendar.DateNum] = {">=$(=$(SelectedDateMinYTM)) <$(=$(SelectedDateMaxYTM))"},ExpenseFlag={'0'}>} Distinct ReservationNumber) >= 0
AND
//Difference between the Q3 of this destination and the 'Overall Q3' has to be bigger than 0
//Normal 3rd Quartile
Avg(Aggr( NoDistinct
Fractile( {< [ReferenceDate.Calendar.DateNum] = {">=$(=$(SelectedDateMinYTM)) <$(=$(SelectedDateMaxYTM))"},ExpenseFlag={'0'}>} ClientNormalizedPrice,0.75)
,[DestinationCity]))
-
//Overall 3rd Quartile
Avg(Aggr( NoDistinct
Fractile( {< [ReferenceDate.Calendar.DateNum] = {">=$(=$(SelectedDateMinYTM)) <$(=$(SelectedDateMaxYTM))"},ExpenseFlag={'0'}>} ClientNormalizedPrice,0.75)
,"Service SubCategory")) > 0
,1,0)

Hoping anybody can help
Greetings Yorick

Re: Filter on Calculated Variable in Set Analysis

Kushal_Chawda — Thu, 29 Oct 2020 12:28:40 GMT

@Yorick_Stevens I don't think set analysis will work here but you can use expression like below

if(
//Service Length has to be longer than 20
Sum({< [ReferenceDate.Calendar.DateNum] = {">=$(=$(SelectedDateMinYTM))<$(=$(SelectedDateMaxYTM))"},ExpenseFlag={'0'}>} ServiceLength) >= 20
AND
//# Booking shas to be bigger than 0
Count({< [ReferenceDate.Calendar.DateNum] = {">=$(=$(SelectedDateMinYTM)) <$(=$(SelectedDateMaxYTM))"},ExpenseFlag={'0'}>} Distinct ReservationNumber) >= 0
AND
//Difference between the Q3 of this destination and the 'Overall Q3' has to be bigger than 0
//Normal 3rd Quartile
Avg(Aggr( NoDistinct
Fractile( {< [ReferenceDate.Calendar.DateNum] = {">=$(=$(SelectedDateMinYTM)) <$(=$(SelectedDateMaxYTM))"},ExpenseFlag={'0'}>} ClientNormalizedPrice,0.75)
,[DestinationCity]))
-
//Overall 3rd Quartile
Avg(Aggr( NoDistinct
Fractile( {< [ReferenceDate.Calendar.DateNum] = {">=$(=$(SelectedDateMinYTM)) <$(=$(SelectedDateMaxYTM))"},ExpenseFlag={'0'}>} ClientNormalizedPrice,0.75)
,"Service SubCategory")) > 0,

[DestinationCity] )

Re: Filter on Calculated Variable in Set Analysis

Yorick_Stevens — Thu, 29 Oct 2020 13:48:45 GMT

Hi Kush,

How would you u suggest to filter on the following measure

Avg(Aggr( NoDistinct
Fractile( {< [ReferenceDate.Calendar.DateNum] = {">=$(=$(SelectedDateMinYTM)) <$(=$(SelectedDateMaxYTM))"},ExpenseFlag={'0'}>} ClientNormalizedPrice,0.75)
,"Service SubCategory"))

Thanks in advance

Re: Filter on Calculated Variable in Set Analysis

Brett_Bleess — Wed, 11 Nov 2020 22:01:01 GMT

@Kushal_Chawda Any chance you can swing back by on this one to check the last update by the poster to see if you can add any further guidance? Thanks in advance as always and sorry we are still having issues with notifications.

Regards,
Brett