topic Re: Limitation Fixed Number Issue with Variance Pct in App Development

Limitation Fixed Number Issue with Variance Pct

williamlau99 — Tue, 15 Oct 2019 14:28:49 GMT

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

Re: Limitation Fixed Number Issue with Variance Pct

anushree1 — Wed, 16 Oct 2019 09:18:13 GMT

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-table/td-p/11371

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

Re: Limitation Fixed Number Issue with Variance Pct

williamlau — Wed, 16 Oct 2019 14:00:00 GMT

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.

Re: Limitation Fixed Number Issue with Variance Pct

anushree1 — Thu, 17 Oct 2019 06:50:24 GMT

Hi,

Can you share the sample data

Re: Limitation Fixed Number Issue with Variance Pct

williamlau — Thu, 17 Oct 2019 17:46:42 GMT

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.

Re: Limitation Fixed Number Issue with Variance Pct

anushree1 — Fri, 18 Oct 2019 06:04:35 GMT

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

Re: Limitation Fixed Number Issue with Variance Pct

williamlau99 — Fri, 18 Oct 2019 15:54:27 GMT

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.