Expression in chart

[carolin01]

Hi,

could anybody help me with this:

sum({$<FYear = {$(vChooseYear1)}, OrderPhase_Desc = {$(vOrderPhaseDesc)}>}SoldToBusinessPartnerCount)

I´m trying to count the number of buying customers for a specific year. The field SoldToBusinessPartnerCount contains 1 for each sales position line. I think sum distinct is correct but for some reason I can´t get this working. Is it possible?

Best regards,

Carolin

[sunilkumarqv]

I think you don't need it  <FYear = {$(vChooseYear1)} it will restrict year so try this

sum({$<FYear = ,OrderPhase_Desc = {$(vOrderPhaseDesc)}>}SoldToBusinessPartnerCount)

[datanibbler]

Hi Carolin,

your set_expression looks generally correct.

- Have you tested (in a textbox on the GUI) whether your variables come out right when you put them in this $()
   thing?

- You might have to replace the $ at the beginning of the set_expression with a 1 to generally disregard the user's selections and place your own filters instead.

- You might have to put those variables (complete with $(), if that's correct) into double_quotes. Try both ways.

HTH

Best regards,

DataNibbler

[carolin01]

The set_expression worked well as it is shown above. But I´m trying to change it to:

Sum(Aggr(DISTINCT({$<FYear = {$(vChooseYear1)}, OrderPhase_Desc = {$(vOrderPhaseDesc)}>}SoldToBusinessPartnerCount)))

This is necessary as I have several orders but I would like to count each customer only one time. Unfortunately I can´t get it working.

[its_anandrjs]

Hi,

I feel your expression is correct for test this expression plot a input box object and add variable there and check its value. Also check with Sum(Distict Your expresion) and check the data also.

Thanks & Regards

[Nicole-Smith]

Why not do something like this:

count({$<FYear = {$(vChooseYear1)}, OrderPhase_Desc = {$(vOrderPhaseDesc)}, SoldToBusinessPartnerCount = {1}>} distinct Customer)

[its_anandrjs]

Hi,

Ok thats right but which dimension you aggregate the sum expression you have to provide that also like

Eg: aggr( sum(Sales), Country )  Here Country is dimension

Check your dimension and put there then check it.

Thanks & Regards

[carolin01]

Distinct Customer made that I got a 1 for each customer. But if I have it in a text box then the total is also 1 and not the number of customer that have bought that year. My next trial was:

aggr ( sum({$<FYear = {$(vChooseYear1)}, OrderPhase_Desc = {$(vOrderPhaseDesc)}>}Distinct SoldToBusinessPartnerCount) )

But also didn´t work as it showed 0.

[Nicole-Smith]

If you're using aggr(), you need to put dimensions into the aggr() function as well.

Maybe something like:

sum(aggr(count({$<FYear = {$(vChooseYear1)}, OrderPhase_Desc = {$(vOrderPhaseDesc)}, SoldToBusinessPartnerCount = {1}>} distinct Customer), Customer))

[carolin01]

This is close now:

sum(aggr(count({$<FYear = {$(vChooseYear1)}, OrderPhase_Desc = {$(vOrderPhaseDesc)}, SoldToBusinessPartnerCount = {1}>} distinct SoldToBusinessPartnerCount), SoldToBusinessPartner))

But still some lines show the value 2 instead of 1 and I´m not sure why? SoldToBusinessParter includes the customer-number. SoldToBusinessPartnerCount includes "1" for each sales line.