
Re: Pivot Table: total row aggregated over dimension
Deepak Vadithala May 11, 2012 4:59 AM (in response to user5674)Hi,
I wanted to make sure if I have understood your question. Do you want to show the Subtotals for each Measure (Field)? I mean in your example do you wanted to see Total for each Category where the calculation is MeasureA + MeasureB, is this right?
If this is incorrect assumption then please can you quickly mockup a table showing what is the output you are expecting.
Thanks,
DV

Re: Pivot Table: total row aggregated over dimension
user5674 May 11, 2012 8:43 AM (in response to Deepak Vadithala)Hi,
I want on "Total  MeasureA" the total of MeasureA rows, same thing for B.
For example:
Expr1 we have MeasureA C1>2, C2>1, C3>1 and C4>0. So on "Total  MeasureA" I want the sum : 2+1+1+0=4
and on "Total  MeasureB" : 0+0+0+0=0
I want Total MeasureA over dimension Category, and the same for MeasureB as in my first table.
Thanks.

Re: Pivot Table: total row aggregated over dimension
Deepak Vadithala May 11, 2012 9:33 AM (in response to user5674)Hi Again,
Now I understand the requirement. Your "Total Temp Solution" table was hidden in your original example and that's the reason why I couldn't follow you completely. Anyway, I had to tweak two things to get this working:
1. I have added "Total" value in the Category field in the Load Script. After reloading the document, it shows up "Total" at the end under Category Dimension and you can also remove it by using calculated expression if it's not required in other objects.
2. I don't like to use IF statements because it's massive overhead when your data fields have high cardinality. However, this still works as a solution and I'll leave it your judgement.
_____________________________________________________________
Here is the expression you should be using :
IF(Measure='MeasureA' and Category <> 'Total',
Count(Distinct Id),
IF(Measure='MeasureA' and Category = 'Total',
sum(TOTAL AGGR(COUNT(Distinct Id), Category)),
0
)
)
_____________________________________________________________
Explanation of above expression :
Step 1 : We are checking both the condition i.e (If the Measure = 'MeasureA' and Category <> 'Total' then we will use your original expression which is :
Count(Distinct Id)
Step 2 : Once the QV engine evaluates "False" for the above expression then we are checking the condition i.e. (If the Measure = 'MeasureA' and Category = 'Total' then we will use this expression :
Sum(TOTAL AGGR(COUNT(Distinct Id), Category))
This expression aggregates and calculates the total. Which is 4 in your example.
Step 3: If both the conditions are not "True" then QV assigns 0. So all the values with Measure = 'MeasureB' having 0.
I'm also attaching the QV document for your reference.
I hope this helps!
Good luck!
Cheers,
DV

218326_Example.qvw 142.2 K

Re: Pivot Table: total row aggregated over dimension
user5674 May 11, 2012 11:58 AM (in response to Deepak Vadithala)Thank you!
It seems a quite good solution.
I have just added this to my load script in order to not disturb other objects using "Category" field without totals:
SET NULLINTERPRET=NULL;
CategoryMap:
LOAD Category, Category as CategoryMap
resident Category;
Concatenate(CategoryMap)
Load * INLINE [
Category,CategoryMap
NULL,Total
];
SET NULLINTERPRET=;I use only for my table with total line the field "CategoryMap" instead of field "Category", and it's perfect.

Re: Pivot Table: total row aggregated over dimension
Deepak Vadithala May 11, 2012 12:06 PM (in response to user5674)I'm happy to know that you have got it working. It was interesting question, which made me think. Thanks for that
Not sure if you have massive application. But if you have massive application then you could do Partial Reload for these kind of scenarios. This is ideal situation to use Partial Reload. Incase, if you wanted more understanding on Partial Reload then please check the below post on my blog :
Have a great weekend.
Cheers  DV

Pivot Table: total row aggregated over dimension
Giampiero Cina May 14, 2012 4:08 AM (in response to Deepak Vadithala)Hi DV,
I was looking for your solution because it is very interesting.
May I ask you a thing!?
Your solution with MeasureA works perfectly.
What about MeasureB? I used your solution with MeasureB but i don't obtain the result 100556,4 .
Am I making a mistake?
I used:
IF(Measure='MeasureB' and Category <> 'Total',
Count(Distinct Id),
IF(Measure='MeasureB' and Category = 'Total',
sum(TOTAL AGGR(COUNT(Distinct Id), Category)),
0
)
)
I'm sorry but your solution is very nice and I would like to use it.
Thanks a lot
Giampiero

Re: Pivot Table: total row aggregated over dimension
Deepak Vadithala May 14, 2012 4:50 AM (in response to Giampiero Cina )Hi Giampiero,
I'm glad that you find this thread interesting and don't have to be sorry. This whole purpose of having this community is to help each other. I learn a lot from all of you guys. So it's okay mate.
I'm assuming that you are talking about Expr2. And the original expression for Exp2 is different from Expr1. So please try the below expression and it should work :
IF(Measure='MeasureB' and Category <> 'Total',
SUM( aggr(NODISTINCT Min(ValueB),Category,Id)),
IF(Measure='MeasureB' and Category = 'Total',
SUM(total aggr(NODISTINCT Min(ValueB),Category,Id)),
0
)
)
I'm also attaching the example for your reference.
Cheers,
DV

218326_Example.qvw 142.5 K

Pivot Table: total row aggregated over dimension
Giampiero Cina May 14, 2012 5:03 AM (in response to Deepak Vadithala)Hi DV,
thank you so much.
I think the same thing about this community.
I often try to help and to be helped :)
You're right, I didn't use the expression 2. Now it works perfectly.
I like a lot your solution. It will be a good start point to solve similar problem in the future.
Again, THANK YOU SO MUCH.
Giampiero

Re: Pivot Table: total row aggregated over dimension
Deepak Vadithala May 14, 2012 5:12 AM (in response to Giampiero Cina )Hi Giampiero,
Great! You are welcome mate. I'll make a video post on this topic and post it on my blog.
Cheers,
DV


Re: Pivot Table: total row aggregated over dimension
user5674 May 16, 2012 4:25 AM (in response to Deepak Vadithala)I had just a little problem because on Category field value selections I lost my Total row.
To solve it I just replace my previous inline table like this:
SET NULLINTERPRET=NULL;
CategoryMap:
LOAD Category, Category as CategoryMap
resident Category;
Concatenate(CategoryMap)
Load * INLINE [
Category,CategoryMap
NULL,Total
];
SET NULLINTERPRET=;By just a list independent of data selections
CategoryMap :
LOAD Category as CategoryMap
resident Category;
Concatenate(CategoryMap)
Load * INLINE [
CategoryMap
Total
];
Then use this field CategoryMap in pivot table first dimension expression as below:
=If(CategoryMap=Category or CategoryMap='Total',CategoryMap)
So the total row don't be hidden if I select C1 and C2 Category value, for example.
And as the "Total" value isn't linked to Category field values expressions are automatically set to total value without the
If(CategoryMap='Total') condition. I have just need to set this condition on total value for expression using AGGR function, not for simple Sum and Count expression.
My row Total isn't hidden now when I do selection on field "Category".
I just see something strange now: why C1 Exr2 measureB is 100001.4 and not 100000.95 ?
min valueB for Id 1 and Category C1 is 0.45 and for Id 9 Category C1 it's 100000.5
Normally the sum must be 100000.95, not 100001.4
I have another question, but without link with this subject:
Is it possible to do something like this on this pivot table?:
MyFirstSuperHeader MySecondSuperHedaer CategoryMap Measure Expr1 Expr2 Expr3 C1 MeasureA 2 0 2 MeasureB 0 100001,4 100001,4 MeasureA 1 0 1 C2 MeasureB 0 0 0 C3 MeasureA 1 0 1 MeasureB 0 555 555 C4 MeasureA 0 0 0 C4 MeasureB 0 0 0 Total MeasureA 3 0 0 MeasureB 0 100556,4 0 Currently I made this with two horrible text object which don't autosize when my pivot table size change.
I have already tried to do some sort of inline table with headers/super headers, but I can't hide/delete column when expressions is null. I had something like this
Parent Header 1 Parent Header 2 Expr1 Expr2 Expr3 Expr2 Expr3 even with "Supress When Value is Null" checked on my "inline" dimension ParentHeader and Expressions defined with a condition like
IF(ParentHeader='ParentHeader1',***) I have all expressions on each of my horizontal dimension values.

Re: Pivot Table: total row aggregated over dimension
user5674 May 16, 2012 6:01 AM (in response to user5674)Hello,
Could someone help me on this 2 subjects above:
 Incorrect expression values on my table
 Super headers for specific expressions.
The first point is the most important. I tried some formula but it doesn't fix it. Perhaps I use bad syntax?
My sum expression don't display the good result.
Example attached.

Example.qvw 126.0 K

Re: Pivot Table: total row aggregated over dimension
user5674 May 16, 2012 11:31 AM (in response to user5674)I have finaly found a formula which give me the good result.
I don't know if there are better formulas to do this.
But now I want my "total" Category field value remain when I do selection on the field Category (directly on the table or by a selection list)
In the application attached here, I have two table (aggregate 1 & 2).
If I select Category values by the list, I have always the good total value on my table aggregate1, but not on the second, aggregate2.
If I select directly on the table aggregate1 dimension, I lose the total rows.
On the second table I lose always the total values when I do selections.
Could you help me? I want the total row remain on my pivot when I select Category field values.
Thanks.

Example.qvw 128.0 K








