Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
johnpaul
Partner - Creator
Partner - Creator

Can we use a custom function to display a total in a table?

On a table, we need to apply a factor to the column totals. This is so that certain data cannot be used to uniquely identify people.

In QlikView, we can add an expression to calculate the total - is there a way to do this with Sense?

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

Yes in a TABLE visualization you can do a check to determine if the calculation is for the Totals position like this:

If( RowNo(TOTAL) = 0 , Sum(xyz)*factor , Sum(xyz) )

View solution in original post

7 Replies
YoussefBelloum
Champion
Champion

Hi,

can you be more clear please ?

what is the expected output ? it will be interesting if you can describe it on a screen shot using the table image.

petter
Partner - Champion III
Partner - Champion III

Yes in a TABLE visualization you can do a check to determine if the calculation is for the Totals position like this:

If( RowNo(TOTAL) = 0 , Sum(xyz)*factor , Sum(xyz) )

johnpaul
Partner - Creator
Partner - Creator
Author

The feature is described here: https://docs.wingarc.com.au/display/SuperSTAR99/Overview+-+Perturbation

This other software applies a function to vary the value of the data, if the result set could allow someone to be identified.

image008.png

What we need to do is to not allow a result of 1, when that actual result would be 1. In the above case, there would be only one person who matches the criteria born in China.

The FACT table is about 50 columns wide and 120M records.

The problem is that the total should be the total of the values displayed in the table, not the real total.

Also, we actually need to alter the total, so that someone cannot 'reverse engineer' a result, by working out the total in a set and then the total in a set with the exclusion of the one record which matches the criteria.

johnpaul
Partner - Creator
Partner - Creator
Author

Hi Petter,

Thanks for the input - I can't seem to make it work.

If I choose a total for the column, then I just get the sum of the rows, if I choose none, I get nothing.

Screen Shot 2018-02-15 at 10.00.46 pm.png

evan_kurowski
Specialist
Specialist

The capability of being able to assign custom formulas to various total cells on a pivot/straight tables is a very appreciated capability.

One side-effect I've observed from this technique is sometimes this will detach the dynamic re-sort capabilities of a straight table (on all columns, and not just the one with the "overridden totals").  So after I enter an expression that does this, then go to the straight table and click on the headers to apply/reverse sort, nothing happens.

The technique is really useful, but is there a way to sidestep impacting sort, and preserve both? ~E

petter
Partner - Champion III
Partner - Champion III

It works for me - I used this in many applications both with QlikView and Qlik Sense. Can't see that it is something wrong with your expression either.

johnpaul
Partner - Creator
Partner - Creator
Author

I tried it again and it worked, I must have keyed a value in wrongly.