Limitation Fixed Number Issue with Variance Pct

[williamlau99]

I have a straight table with only one dimension, Retailer, and I have the Limitation set to a value in a variable. The value in the variable is controlled by using a slider.

Along with the dimension, I have measures for the following: this year's sales, last year's sales, the variance and the variance percentage.    The variance is the  difference between this year and  last year's sales.  The variance percentage is the variance divided by last year's sales. 

On each row, the variance and the variance percentage is correct.  However on the total row, the variance percentage is incorrect.  The variance percentage is based on the variance and  last year's sales FOR ALL retailers.  Changing the  slider value has no impact on the total line's variance percentage.  When I export the table to Excel and calculate the variance percentage for the entire table, I get a different number.

Some one suggested that I change the "Totals Function" on the measure to "Sum" but that had no effect.

The calculation for "Variance %" is really simple (see below) and its correct on each of the row but the total line.

[Variance] / [Prev Year]

All RetailersOnly the Top 5 Retailers

[anushree1]

Please use Dimensionality() Function along with If to get the desired calculation to get the correct results

Please check : https://community.qlik.com/t5/QlikView-App-Development/How-to-show-percentage-of-subtotal-in-pivot-t...

It help you understand the usage and you could apply this as per your needs

[williamlau]

Sorry. That did not work for me. I converted by straight table to a pivot table. I don't see how a dimensionality function would work for me since I only have 1 dimension. Plus I used the "TOTAL" parameter and I specified the name of my dimension in the expression. I don't have problems with the percentage in the detail row. The problem is on the "Totals" line.

[anushree1]

Hi,

Can you share the sample data

[williamlau]

I've attached a qvf with some sample data. There are only 2 visualization object on the sheet. The table and a slider. The data comes from an attached file. The version of Qlik Sense that I am running in my office is February 2019.

[anushree1]

Could you remove the slider object and also remove the selections if any and share the file.

As,the file looks like below or me.

It would also help if you could share the sample excel file too

[williamlau99]

Here it is again.  I removed the slider.  

To change the limitation, you need to go into Edit mode and change the limitation size for the Retailer dimension in the table.  Currently, it is set at 10 which is the exactly the number of records in the data model.

As you reduce the limitation size, notice that the percentage variance on the total line does not change.  The total for the  other metrics  like the "Curr Year", "Prev Year" and "Variance"  adjust to the change in limitation size which makes sense.