Sum hidden sub hierarchies / Cut down number of iteration pages
I have had a case where I had to disregard some product groups, when certain conditions on ItemIDs within the product group weren't meet. I think it might be useful for others as well, as the idea is also useful for iterations.
Lets say I want to know how many Items in a product group has sales > than X. And I don't care about the results per itemid, I just want to know the total for the group.
First I make a calculation that works as a filter.
if sum(d1,0,m1) > x then 1 else 0
This will mark all the item ids where an ItemID is greater than x.
Next I make a calculation that sums the aforementioned results on grouplevel.
if sum(c1, all(c), 0) = 0 then 0 else sum(c1, all(c), 0)
What it does is that all the Item id's get 0, and the product group will get the summed values, of the filter result we calculated before.
Then I hide all the itemid names and values
And then I use visibility agent on the group sum I made before:
Value = 0
It will then hide all the product groups, that doesn't have any Itemids with sales > X.
I found it especially useful for iterations, as I can then cut down the pages that will get printed.
Comments
Please sign in to leave a comment.