HELP - Generating Percentage Change on Straight Tables in QlikView Standard

[mestevens499]

Hi Qlik Community,

I'm hoping someone on here can assist with what should be a simplified process of generating percentage change within a straight table using QlikView standard edition (NOT QlikSense.  Apologies for posting this in the 'New to QlikSense' forum as I couldn't find anything else suitable in which to post this under), as so far I have tried different script variations within the expression part of the table and it is now starting to make me lose my sanity in figuring out what is wrong with it.

So...

I am creating a straight table - not a Pivot one - which shows figures (calculated expression definition) for each year against a column of UK counties.  Next to each year column are % change columns (also expression calculated definition).

The calculation for each column should be - Year / Total Year (Total UK Counties) for each County.

As the attached image shows - Table A is what is currently being displayed as a result of the expression definition I've generated.  As you can see whilst 'Total UK Counties' is showing 100% in each of the year %age change columns - which is correct.  However it has replicated the results of the individual County %age changes across all year columns.

Table B is how it should look.  (P.S. I have only extracted a small portion of the data to demonstrate what is happening and doesn't show the full range of data to equate to 100%).

So far the expression definition I have used for each %age change column includes:

=[2017/18]/Sum(Total<[2017/18]>(Input)) - please note I change the years accordingly per column.

I have even tried to use or insert the ABOVE and COUNT commands intot the definition to see if it'll help but this doesn't seem to work either.  I've even tried it without the Total Input column being there but to no avail.

I hope I have explained clearly what I am hoping to achieve with this.

Any help you can provide would be gratefully recieved.  Thanking you in advance.

Mike.

[Oleg_Troyansky]

Hi Mike,

I think the issue is in the use of column labels within the TOTAL qualifier - that's not allowed by the syntax rules.

You can only use field names, and only those that are used as chart dimensions, in combination with TOTAL, like this:

sum(TOTAL <Dim1, Dim2, ...> Value)

So, for your calculation, you should use the same formula that you use for each Year, and then use the same formula with the TOTAL qualifier (without any dimensions) in the denominator. Something like this, for example:

sum({<Year={2023}>} Value) / sum({<Year={2023}>} TOTAL Value)

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

Also check out my book QlikView Your Business - as a beginning QlikView developer, you can learn a lot of advanced QlikView stuff with it!