Slider and aggregation

[#1]

I have a customer table with a sales expression:

Sum({$<Year={$(=max(Year))}>}
 if(CurrNo=1,Invoice_NetPriceEUR,if(CurrNo=2,Invoice_NetPriceLocal,Invoice_NetPriceSE)))

and a gross margin expression:

sum({$<Year={$(=max(Year))}>}
 Invoice_MarginSE) / sum({$<Year={$(=max(Year))}>} Invoice_NetPriceSE)

These works as expected.

Now, I have a second table where I use a slider object to filter out some customers based on sales limit. So the first expression, that works, looks like this:

sum({$<Year={$(=max(Year))}>}
if(
 Customer_New=1 and aggr(Sum(if(CurrNo=1,Invoice_NetPriceEUR,if (CurrNo=2,Invoice_NetPriceLocal,Invoice_NetPriceSE))),Customer_No) > vOrderSizeLimit,
 aggr(Sum(if(CurrNo=1,Invoice_NetPriceEUR,if (CurrNo=2,Invoice_NetPriceLocal,Invoice_NetPriceSE))),Customer_No)
 )
 )

This works as intended. But now I struggle with the gross margin expression and I can't make it work. My suggestion was:

sum({$<Year={$(=max(Year))}>}
if(
 Customer_New=1 and aggr(Sum(if(CurrNo=1,Invoice_NetPriceEUR,if(CurrNo=2,Invoice_NetPriceLocal,Invoice_NetPriceSE))),Customer_No) > vOrderSizeLimit,
 aggr(sum( Invoice_MarginSE),customer_No) /
 aggr(sum( Invoice_NetPriceSE), customer_No)
 )
 )

This gives me nothing, though. I've tried a few other variants but know I feel quite lost. Any help would be appreciated.

[#2 · Accepted solution]

I solved the issue with

sum({$<Year={$(=max(Year))}>}
if(
 Customer_New=1 and aggr(Sum(if(CurrNo=1,Invoice_NetPriceEUR,if (CurrNo=2,Invoice_NetPriceLocal,Invoice_NetPriceSE))),Customer_No) > vOrderSizeLimit,
 aggr(sum( Invoice_MarginSE),Customer_No)
 )
 )

 /

sum({$<Year={$(=max(Year))}>}
if(
 Customer_New=1 and aggr(Sum(if(CurrNo=1,Invoice_NetPriceEUR,if (CurrNo=2,Invoice_NetPriceLocal,Invoice_NetPriceSE))),Customer_No) > vOrderSizeLimit,
 aggr(sum( Invoice_NetPriceSE),Customer_No)
 )
 )

[#3]

Hello Hákan,

Can you help me too? I am using the formula below for my TOP 20 customers. (And which percentage they are in the total)

(CONCERN_NAAM = client name and OMZET = SALESAMOUNT)

However, I want to have the real 80/20, so I want to take the 20 % of the clients.

I have found these formulas, but I am not succeeding in placing them correctly

Top 80%: if(sum(Sale)> fractile(total aggr(sum(Sale),Name),.2),sum(Sale))
Bottom 20%: if(sum(Sale)<=fractile(total aggr(sum(Sale),Name),.2),sum(Sale))

Can somebody change the formula below, to reflect the top 20% of the CONCERN_NAAM ?

Thank you

='80/20
'&
num(
sum(total <Periodenummer,CONCERN_NAAM> aggr(
if(
rank(sum(OMZET))<=20
,
sum(OMZET))
, CONCERN_NAAM))

/
sum(total OMZET)
,'###.##0,00%', ',' , '.' )

[#4]

Hi Wouter .

Try to use the ParetoSelect function in a VB macro (triggered by a Button object, or anything else you desire).

Check out this example (VB Macro) which triggers a pareto selection by the Product dimension, according to the ApprovedQuantity measure.

sub pareto_by_product

set obj = ActiveDocument.GetSheetObject("BU01")

set prop = obj.GetProperties

set f = ActiveDocument.GetField ("FullProdName")

if prop.Text.v="Pareto By Product" then

f.ParetoSelect "sum(ApprovedQuantity)"

prop.Text.v = "Undo Pareto By Product"

'prop.Type = 2

obj.SetProperties prop

else

f.clear

prop.Text.v = "Pareto By Product"

obj.SetProperties prop

end if

end sub

Regards,

Montal.

[#5]

Hi Montal,

Thank you for the answer. However, I have implemented the button succesfully, and the text button showing the percentage, still doesn't work.

I am going to make a demo to show where my problem lies.

Thanks!

Wouter

[#6]

Okay, here is the example.

In the two green blocks you can see where I struggle.

One shows the percentage of the top 20 customers,

the other one should show the percentage of the top 20% of customers.

It ain't working 🙂

Thanks

Wouter

[#7]

Hi.

I saw the example and still recommend you to try the alternative of the VB macro, why not use an already existing feature?

[#8]

I think if I use this function, I get the percentage of clients that 20% of the customers do with us?

= num ( if (sum(OMZET)> fractile(total aggr(sum(OMZET),CONCERN_NAAM),0.8),count(Distinct(CONCERN_NAAM )))

/

count

,'###.##0,00%', ',' , '.')

(distinct ID_RELATIE )