Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
danielrogra
Creator
Creator

Using total in a pivot table

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:

Captura.PNG

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!

12 Replies
agigliotti
Partner - Champion
Partner - Champion

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

swuehl
MVP
MVP

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

danielrogra
Creator
Creator
Author

Hi Andrea.

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

Thanks!

danielrogra
Creator
Creator
Author

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
MVP
MVP

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
Creator
Creator
Author

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

Thanks!

danielrogra
Creator
Creator
Author

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

Thanks!

danielrogra
Creator
Creator
Author

Any help ?

agigliotti
Partner - Champion
Partner - Champion

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