I have a hierarchy item called [discretionary], it is a list of discount types. I need to be able to create two sets. one a fixed list and the other what is left over using the items children.
I have created a set called [store/acct Mgr] that is the fixed list and I have created a second set using the except.
I have
set([Acc Manager / SAS],[Manager's Authority],[Offer Not Working],[Faulty / Damaged],[Short Dated],[Discontinued],[Price Match])
and
except(children([Discretionary]),[Store/Acct Mgr])
Both these return lists of items when all I actually want is a single aggregate total to appear.
Can anyone advise the best way to proceed.
Quote from: mrmo on 06 Oct 2015 05:21:00 AM
I have a hierarchy item called [discretionary], it is a list of discount types. I need to be able to create two sets. one a fixed list and the other what is left over using the items children.
I have created a set called [store/acct Mgr] that is the fixed list and I have created a second set using the except.
I have
set([Acc Manager / SAS],[Manager's Authority],[Offer Not Working],[Faulty / Damaged],[Short Dated],[Discontinued],[Price Match])
and
except(children([Discretionary]),[Store/Acct Mgr])
Both these return lists of items when all I actually want is a single aggregate total to appear.
Can anyone advise the best way to proceed.
Hi,
You can take each resultant set of members and include it in an aggregate() function to obtain the aggregated measure value for each set - would that work?
eg
aggregate(currentMeasure within set set([Acc Manager / SAS],[Manager's Authority],[Offer Not Working],[Faulty / Damaged],[Short Dated],[Discontinued],[Price Match]))
aggregate(currentMeasure within set except(children([Discretionary], set([Acc Manager / SAS],[Manager's Authority],[Offer Not Working],[Faulty / Damaged],[Short Dated],[Discontinued],[Price Match])))
Cheers!
MF.
Thanks, I thought there would be a "group by" expression. Seems to work fine.