Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I tried to find an answer to this from the existing discussions, but couldn't find a match.
From a bunch of different kinds of postings over a range of several years, I have successfully aggregated the Costs by Order IDs. Now I would need to exclude Order IDs for which the total Cost is zero when they are grouped by Project IDs.
In the example table below, the Order IDs A1 and A2 should be excluded, because the total Costs is zero when they are grouped by Project IDs (+5 -5 = 0). The Order IDs B1 and B2 should be visible, because for them the sum of Costs by Project ID is +3 -2 = +1, which is of course <> 0.
Thanks for any help in advance
Br
Kimmo
Project ID | Order ID | Posting Year | Costs |
---|---|---|---|
A | A1 | 2017 | +5 |
A | A2 | 2018 | -5 |
B | B1 | 2017 | +3 |
B | B2 | 2018 | -2 |
P.S. It's my first post and I'm not sure if this is a suitable Group where to post it. If there is a newbie forum somewhere for simple questions kindly let me know. Thanks.
I verified that the formula below works. Use it as the dimension for Project ID and be sure to uncheck "Include null values".
=Aggr(If(Sum(Costs) > 0, Only([Project ID])), [Project ID])
(Note: If you also want to allow total project values less than zero, then change the ">" to "<>".
It will also ensure that the filter bar shows "Project ID" if you click on that column for selection.
Maybe:
if(Sum(Aggr(Sum(Costs),[Project ID]))>0,Sum(Aggr(Sum(Costs),[Project ID])),null())
and then exclude nulls
I verified that the formula below works. Use it as the dimension for Project ID and be sure to uncheck "Include null values".
=Aggr(If(Sum(Costs) > 0, Only([Project ID])), [Project ID])
(Note: If you also want to allow total project values less than zero, then change the ">" to "<>".
It will also ensure that the filter bar shows "Project ID" if you click on that column for selection.