Dynamic dimension with Aggr() and Set Analysis?

[Jason_Michaelides]

Hi all,

I'm tearing my hair out as I feel so close to the solution but can't grasp it!  Hope you can help...

I have 3 tables - STAFF, CLIENT and SALES.

• STAFF has StaffName and StaffID
• CLIENT has ClientName, ClientTier and ClientID
• SALES contains ClientID and StaffID as well as all the other details you'd normally associate with a sales table.

I'm trying to get a pivot table with dimensions of ClientTier and ClientName and a SUM(Sales) expression.  Nothing from STAFF table is in the chart dimension.

HOWEVER!  Let's say I have selected StaffName = John,Jane, as well as ClientTier = Premium.  My pivot table must show ALL Premium clients, whether or not John or Jane have made any sales to them.  Any SUM(Sales) figures must only reflect John and Jane's sales, though.

Selecting "Show All Values" is no good as I only want Clients in the currently selected tier.

I've tried all kinds of set analysis in the expression but not quite got their.  I'm wondering if I need to use Aggr() in the dimension but haven't got that working either.

Again - hope you can help.

Thanks,

Jason

[#2]

Hello Jason,

may be someone will have a better solution soon. Meanwhile check out my example app including a workaround. It shows an additional "empty" column with a white textcolor.

HtH

Roland

[Jason_Michaelides]

Hi Roland - thank you for your answer.  Unfortunately I actually have a third dimension (month) that goes across the top of the pivot table so an extra column is very noticeable.  It also messes up Excel exports.  However, this led me to thinking about what causes the full list of dimension values to be displayed, and with a few hours of fiddling I got to:

=IF(SUM(Sales)=0,0,

RangeMax(SUM({1<ClientTier=P()>} Sales)*0+0.00001,SUM(Sales)))

It seems the first part of RangeMax() - i.e. the set -  forces QV to consider all rows as a value.  So this works perfectly!

Thanks for the help - set me down the right road.

Jason

[#4]

Hi Jason,

glad to help you. My part was in evidence the smaller one, but sometimes a hitch into the right direction . . .

RR