Problem with aggr() or only()

[ericheleine]

Hi every body,

My goal is to calculate the total cost only with bank where i have an interaction

I don't arrive to fix this issue with the function aggr

Bank	Interaction per month
Bank1	15
Bank2	12
Bank3	5
Bank4	4
Bank5	0
Bank6	0
Bank7	0

Bank	Cost
Bank1	10000
Bank2	10000
Bank3	10000
Bank4	10000
Bank5	10000
Bank6	10000
Bank7	10000

Total cost expected is 40000

thanks for your help

[tresesco]

Try something like:

Sum( {<[Interaction per month]={'>0'}>} Cost)

[sunny_talwar · Accepted solution]

Or may be this if you don't have a pre-aggregated Interaction per month column

Sum({<Bank = {"=Count(Interaction) > 0"}>} Cost)

[ericheleine]

thank you Tresesco for your quick answer.

that doesn't work because Interaction per month is a formula:
=count(interaction)

The set analysis doesn't work in this case

eric

[tresesco]

Then Sunny's suggestion below should work.

[ericheleine]

thank sunny

works perfectly

regards

[sunny_talwar]

Do you have just Bank as dimension or do you have Bank and Month as dimension? If you just have Bank as dimension, then you can try this

Sum({<Bank = {"=Count(Interaction) > 0"}>} Cost)

If you have Bank and Month as dimension, then may be create a new field in the script like this

LOAD Bank,

    Month,

    Bank&Month as Key

    ....

FROM ....;

and then this

Sum({<Key = {"=Count(Interaction) > 0"}>} Cost)