topic Re: Average Expression from Totals in QlikView

Average Expression from Totals

Mon, 24 Oct 2016 18:17:14 GMT

Hello.

I currently have a really small table that has only 3 expressions.

I wonder, if its possible for this table to have the % of the Total as a Total.

For example, i want my table to be:

Header 1	Header 2	Header 3
Top Performer	20	59%
Mid	6	18%
Under	8	23%

Any Ideas?

thanks,

Re: Average Expression from Totals

sunny_talwar — Mon, 24 Oct 2016 18:34:38 GMT

Assuming your expression to be Count(Measure), you can try this:

Count(Measure)/Count(TOTAL Measure)

Re: Average Expression from Totals

Mon, 24 Oct 2016 19:28:05 GMT

This would change the result of the expression and the total, im looking for a way to change only the total, not the result in the expression.

I cant use Dimensionality beacuse there are no dimensions.

Re: Average Expression from Totals

sunny_talwar — Mon, 24 Oct 2016 20:01:07 GMT

Use Dimensionality to do this

How to use - Dimensionality()

The second dimension... or how to use secondarydimensionality()

Re: Average Expression from Totals

Mon, 24 Oct 2016 20:02:27 GMT

I cant because there are no dimensions on the table, so its the same Dimensionality for Totals and for the expression.

Re: Average Expression from Totals

sunny_talwar — Mon, 24 Oct 2016 20:16:22 GMT

Not sure I understand you setup, would you be able to share a sample to show how your table is setup?

Re: Average Expression from Totals

johnw — Mon, 24 Oct 2016 20:27:28 GMT

You can add a dummy dimension like ='Total' to force dimensionality() to work. But that's probably not ideal.

I gather you have separate expressions for each row? Like:

Top Performer = sum(Blah)
Mid = sum(Something)
Under = sum(Other)

Can you modify the data model in a way that Top Performer, Mid, and Under are dimension values? Or are they dependent on selections? If the latter, you could still create an island dimension not connected to anything, [Performance Category], say. You could use that as a dimension, and something like this as the expression.

pick(match([Performance Category],'Top Performer','Mid','Under'),sum(Blah),sum(Something),sum(Other))

And then have a second expression =column(1), and checkmark Relative, and don't have totals. I haven't tried it, but it seems like it could work. If you can't touch the data model at all, then you could use a valueloop() instead of the island dimension. Not sure this is any better than forcing dimensionality() to work with a dummy dimension, but it's an option.