Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mplautz
Contributor II
Contributor II

Using Totals in Set Analysis to Include Nulls Even when Null Values are not Included

I have a Pivot Table View that breaks down revenue per account and offering type. The rows are account and the columns are offering type and then offering. Doing this calculation is pretty straightforward. This is what it looks like:

 Offering Type 1   Offering Type 2 Offering Type 3
 Offering 1Offering 2Offering 3Offering 4Offering AOffering BOffering
Account 1-$41,000----$15,026
Account 2---$850--$8,027
Account 3---$1,200--$2,048

 

Note that I am not showing every possibility of revenue per account. I am limiting it to certain categories where the Offering Type is defined. For the categories I am not showing, the Offering Type and Offering is NULL in the data. I have a simple revenue formula that is:

Sum({<Country={'US'} >}revenue)

Now, I want to add a second calculation within this pivot table that shows the proportion of revenue each offering has relative to the total amount of revenue per account, even outside the categories shown.  To do this, I just need to start with calculating the total revenue per account. What I would think to do is to do a calculation where I ignore the offering dimensions. I looked online and discovered how to use the TOTAL construct within the set analysis. I modified my Set Analysis to instead ignore everything but Account and Country:

Sum({<Country={'US'} > TOTAL <Account, Country>}revenue)

This produces an unexpected result, because it only showing the total per account of the data that is shown, and not all data, where Offering Type and Offering are NULL. Here's an example, adding that second calculation to the table:

 Offering Type 1       Offering Type 2   Offering Type 3 
 Offering 1 Offering 2 Offering 3 Offering 4 Offering A Offering B Offering 
 RevTotalRevTotalRevTotalRevTotalRevTotalRevTotalRevTotal
Account 1--$41,000$56,026--------$15,026$56,026
Account 2------$850$8,877----$8,027$8,877
Account 3------$1,200$3,248----$2,048$3,248

You will see that the Total value is the same for all non-null cells per row (i.e. per account). For example, in the first row, the value under Total is $56,026, which is the sum of all of the individual Rev columns, $41,000 + $15,026, and so on.

Now, the problem can be overcome by going back and checking "Include Null Value" on both the Offering Type and Offering dimensions. The Total column now shows the correct, expected value:

 - Offering Type 1       Offering Type 2   Offering Type 3 
 - Offering 1 Offering 2 Offering 3 Offering 4 Offering A Offering B Offering 
 RevTotalRevTotalRevTotalRevTotalRevTotalRevTotalRevTotalRevTotal
Account 1$328,402$384,428--$41,000$384,428--------$15,026$384,428
Account 2$204,670$213,547------$850$213,547----$8,027$213,547
Account 3$94,270$97,518------$1,200$97,518----$2,048$97,518

You will again see that the total is the same for all non-null cells per row (i.e. per account).

So I have fixed one problem and then created another one. What I really want is to be able to 

  • calculate the total revenue per account
  • while hiding the data where Offering Type and Offering are NULL
  • on a pivot table

So my question is, how do I use totals in the set analysis to include null dimension values in the calculation, even when the null dimensions are not included in the view?

Labels (1)
0 Replies