Chart Aggregation Behavior

[#1]

My users are asking for something very weird which I'm having a difficult time producing in QV.  I believe the solution is a combination of the aggr() function and set analysis, which I've tried but can't nail it.  The best way I can explain it is by including a sample .qvw attachment and the screen print below.

The root of the problem is that I'm trying to work with an aggregated field that returns results (rows) that I don't want to display.  Even creating the attached example had me confused If anybody can help I'd appreciate it, I think if you open the .qvw you will see exactly what I'm talking about.

Thanks

[johnw · Accepted solution]

If I understood the requirement, I think this:

if("Reg 'A'  Type '1'  Units",sum({<Region={'A'},Type={'2'}>} total <Region,QTR> UNITS))

[#3]

John you're great.  Yep I knew it was something simple like that ... sometimes I forget about combining if/then statements with set analysis or aggr function or TOTAL operator.

Here is the final expression if anybody is interested ...

if

(Type = 1,sum({$<Region = {'A'},Type = {2}>} TOTAL <QTR,Region> UNITS))

[johnw]

You mentioned in a private message that this wasn't totaling.  I actually thought about that later, but wasn't sure what total you'd actually want.  So for the example, what total DO you want?  I'm guessing that for the total, you want to ignore the duplicate 1729, so have 1729 + 1 + 20 = 1750?  Or do you just want sum of rows?  If sum of rows:

sum(aggr(nodistinct if(Type=1,sum({<Region={'A'},Type={'2'}>} total <QTR,Region> UNITS)),QTR,Region,Type))

Now, if you remove the nodistinct, you get a sum of 1750, but you also only display the 1729 on one row.  I'm not seeing quickly how to solve it, so I'll leave it for now, at least until you confirm that you want a 1750 total instead of a 1750 + 1729 = 3479 total.  Well, one way to solve it would be to check the dimensionality and use one expression for the total line and another for the detail lines, but that seems like overkill, and there's surely a better way.

[#5]

John you are correct on all accounts.  I need to show a value for each line and then, for a total, the distinct amount.  The only way I see doing this is like you said, checking the dimensionality which is a little ugly but gets the job done.  Below expression gives me exactly what I need.  You should see though what I need to do in my actual .qvw vs this simple sample.  The measure is a derived measure so you can imagine for every measure I bring in to create the expression I need to add all of this logic.  Wish there was an easier way.

Thanks again

if

(Type = 1,sum({$<Region = {'A'},Type = {2}>} TOTAL <QTR,Region> UNITS),

sum

(aggr( if(Type=1 and Dimensionality() = 3,sum({<Region={'A'},Type={'2'}>} total <QTR,Region> UNITS)),QTR,Region,Type)))