Using total in a pivot table

[danielrogra]

Hi all

I have the following issue I don't know how to solve it:

I've created one fact table with two kind of data using concatenate. Most of the columns are in both subset but there is one that is only in one subset (Let's say variable A only exists in type A).

I want to create the following table:

The problem I see is that if I have let's say 20 variables in common and I want to put all of them in the pivot table I have to write the following code:

sum({<tipo={'TypeB'},VariableA=>} Total <Year,Year-Month,Variable B,Variable C,Variable D,Variable E......> total)

Is there another way to do that?

Thanks you!

[agigliotti]

could you provide a sample app with mock data showing what are you trying to achieve?

[swuehl]

Maybe a link table linking two fact tables instead of concatenating all facts into one could be an alternative.

[danielrogra]

Hi Andrea.

I'll try to do it (The data I'm using I can't upload it)

Thanks!

[danielrogra]

Hi Stefan

I guess I would have the same problem as I want to show the total for each value of variable A as it only exists in the subset "Type A", but if I use variables that are in both subsets I want to show the value by those variables. Am I right?

[swuehl]

The idea would be to create a table with the common variables and another with the Type A only variables, linked by the common key (Year-Month?).

Not 100% sure if this solves your issue. But a selection in Type A or using Type A as dimensions should still get all other variables' values, without duplicating any values in aggregation.

[danielrogra]

I've attached a simple app to show you my Issue.

Thanks!

[danielrogra]

I've attached a simple app to show you my Issue.

Thanks!

[danielrogra]

Any help ?

[agigliotti]

regarding your pivot table, what's your desired output instead ?