Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
March 26 at 10am ET: See how Qlik drives growth and value in ISV segment - REGISTER NOW
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
JamGardner
Contributor III
Contributor III
‎2025-02-13 11:47 AM

Master Measure Calculations

Hi All, 

I have 2 master measures - the first is a basic sum of a few fields , the other is a ratio calculation - sum divided by another dimension. I created these to allow me to do a the master measure 1/ master measure 2 in the visualisation. This all worked fine during the dev stage as I was using excel which had the different tables loaded up separately 

However the more data I added the longer the load took. As such I created a QVD loading all the data together creating a Table ID dimension in the data table to allow me to continue the calculations. Again all seemed fine on basic calculation's using set analysis to identify the table ID to use.

Sometimes within the data the dimensions that make up the master measures have a 0 value, now this was never a problem when I used to load up each Tab from excel separately, but now it seems to interfere with the calculations of the master measure giving me null ("-") values. Is there any way round this or do I need to go back to a load of separate tables (QVDs)

 

 

 

 

 

 
Labels (3)
Labels
148 Views
1 Solution

Accepted Solutions
JamGardner
Contributor III
Contributor III
‎2025-02-14 04:36 AM
Author

In response to morgankejerhag

Thanks for your help, I have managed to find a solution by altering my master measure to add an IF Statement to ignore the Zero's in one of my master measures

Sum({<TableID={'Volume'}>} Bundled_1) /
IF(Sum({<TableID={'Share'}>} Bundled_1) = 0, 1, Sum({<TableID={'Share'}>} Bundled_1)) +

Sum({<TableID={'Volume'}>} Bundled_2) /
IF(Sum({<TableID={'Share'}>} Bundled_2) = 0, 1, Sum({<TableID={'Share'}>} Bundled_2)) +

Sum({<TableID={'Volume'}>} Bundled_3) /
IF(Sum({<TableID={'Share'}>} Bundled_3) = 0, 1, Sum({<TableID={'Share'}>} Bundled_3)) +

Sum({<TableID={'Volume'}>} Bundled_4) /
IF(Sum({<TableID={'Share'}>} Bundled_4) = 0, 1, Sum({<TableID={'Share'}>} Bundled_4)) +

Sum({<TableID={'Volume'}>} Bundled_Other) /
IF(Sum({<TableID={'Share'}>} Bundled_Other) = 0, 1, Sum({<TableID={'Share'}>} Bundled_Other))

View solution in original post

101 Views
0 Likes
2 Replies
morgankejerhag
Partner - Creator III
Partner - Creator III
‎2025-02-13 01:04 PM

It should be possible to solve, but it is a bit unclear what the problem actually is and what you are trying to achieve. Maybe you can explain more how the data model is, how your measure is and what you want to calculate.

 

 
127 Views
0 Likes
JamGardner
Contributor III
Contributor III
‎2025-02-14 04:36 AM
Author

In response to morgankejerhag

Thanks for your help, I have managed to find a solution by altering my master measure to add an IF Statement to ignore the Zero's in one of my master measures

Sum({<TableID={'Volume'}>} Bundled_1) /
IF(Sum({<TableID={'Share'}>} Bundled_1) = 0, 1, Sum({<TableID={'Share'}>} Bundled_1)) +

Sum({<TableID={'Volume'}>} Bundled_2) /
IF(Sum({<TableID={'Share'}>} Bundled_2) = 0, 1, Sum({<TableID={'Share'}>} Bundled_2)) +

Sum({<TableID={'Volume'}>} Bundled_3) /
IF(Sum({<TableID={'Share'}>} Bundled_3) = 0, 1, Sum({<TableID={'Share'}>} Bundled_3)) +

Sum({<TableID={'Volume'}>} Bundled_4) /
IF(Sum({<TableID={'Share'}>} Bundled_4) = 0, 1, Sum({<TableID={'Share'}>} Bundled_4)) +

Sum({<TableID={'Volume'}>} Bundled_Other) /
IF(Sum({<TableID={'Share'}>} Bundled_Other) = 0, 1, Sum({<TableID={'Share'}>} Bundled_Other))

102 Views
0 Likes