topic Re: Is this possible in a pivot table in QlikView? in App Development

Is this possible in a pivot table in QlikView?

NenadV — Wed, 10 Jan 2024 19:07:29 GMT

How to show "Materials Variance as % of Standard Material" in the table above calculated as Materials Variance /Standard Material?

Thanks

Re: Is this possible in a pivot table in QlikView?

anat — Thu, 11 Jan 2024 02:27:30 GMT

ca you please elaborate more about your requirement...

to show the numbers in percentage you can use like Num(sum(sales),'0.0%')

Re: Is this possible in a pivot table in QlikView?

NenadV — Thu, 11 Jan 2024 09:17:17 GMT

Thanks, this is not about format but how to read amount in one line and divide it by amount from another line.

Re: Is this possible in a pivot table in QlikView?

marcus_sommer — Thu, 11 Jan 2024 11:37:17 GMT

To access any row/column you may use interecord-functions like above(). If not a single row/column is queried you will need to wrap it into a range-function like rangeavg().

But I assume you want to calculate certain rates against the next higher dimension-levels. n such case you may use a TOTAL statement like:

sum(Value) / sum(TOTAL Value)

Re: Is this possible in a pivot table in QlikView?

NenadV — Tue, 16 Jan 2024 10:47:44 GMT

it looks like there is no easy solution to this.

Re: Is this possible in a pivot table in QlikView?

marcus_sommer — Tue, 16 Jan 2024 11:01:43 GMT

Maybe not so easy as you want it to be as a one-click-solution but it will be probably possible with slightly extended expressions.

Re: Is this possible in a pivot table in QlikView?

NenadV — Tue, 16 Jan 2024 11:12:50 GMT

can you please share one? Thank you

Re: Is this possible in a pivot table in QlikView?

marcus_sommer — Tue, 16 Jan 2024 11:33:27 GMT

I hinted above different ways. It would be useful if you elaborate it in more detail which source-data should return which results.

Re: Is this possible in a pivot table in QlikView?

NenadV — Tue, 16 Jan 2024 14:45:54 GMT

Marcus, thank you for interest.

I need to show Overhead Variance / Standard Overhead *100, i.e.465/1259*100 = 37%

Re: Is this possible in a pivot table in QlikView?

marcus_sommer — Tue, 16 Jan 2024 15:44:30 GMT

You may try it with an interrecord-function approach like:

above(TOTAL YourExpression, RowOffset)

whereby RowOffset could be a calculation counting the number of available dimension-values to access the target-row. By a complex chart it might be not trivial.

If this isn't practically you need to associate the relevant dimension-values in a way that they support such a calculation. Without knowing your dataset and the majority of your requirements in depth it's not possible to suggest a more detailed approach. In general you should consider to simplify the object maybe by using a different structure and/or distributing the information to several objects.

Re: Is this possible in a pivot table in QlikView?

NenadV — Tue, 16 Jan 2024 16:00:04 GMT

Thank you!

Keep safe!