- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Problem with aggr() or only()
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Or may be this if you don't have a pre-aggregated Interaction per month column
Sum({<Bank = {"=Count(Interaction) > 0"}>} Cost)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try something like:
Sum( {<[Interaction per month]={'>0'}>} Cost)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Or may be this if you don't have a pre-aggregated Interaction per month column
Sum({<Bank = {"=Count(Interaction) > 0"}>} Cost)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Then Sunny's suggestion below should work.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
thank sunny
works perfectly
regards
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)