topic Re: Pivot Table expression in QlikView

Pivot Table expression

tomcatxx — Wed, 25 Nov 2020 16:16:04 GMT

Hi, there. I want to make a pivot table like that. There are only 2 dimensions 'Minifabrik' and 'Day'.

Minifabrik	We	Th	Fr	Avg	Target
MF1	%	%	%	%	%
MF2	%	%	%	%	%
MF3	%	%	%	%	%
MF4	%	%	%	%	%

I made a table but not finished.

Values for column We Th and Fr are Sum(A)/Sum(B).

Can u help me to write expression for column Avgerage and Target? Target = avg(T)

Thank you.

data source

Load Minifabrik,

Day,

From xx.qvd

Re: Pivot Table expression

niclaz79 — Fri, 10 Aug 2018 10:22:12 GMT

Avg(RangeSum(Before(Sum(A)/Sum(B),0,7)) maybe?

Re: Pivot Table expression

suhaskharat — Fri, 10 Aug 2018 10:24:31 GMT

Use below formula for average

Column(1)+Column(2)+Column(3)/3

for target as u have mentioned

(Column(1)+Column(2)+Column(3)/3)/100

Re: Pivot Table expression

tomcatxx — Fri, 10 Aug 2018 10:28:15 GMT

Thank u, but it doesn't work.

Re: Pivot Table expression

tomcatxx — Fri, 10 Aug 2018 10:33:55 GMT

Can u you write Expression for that? And sometimes I have data for 7 days(Mo~So). In this table, the null value just will not be showed.

Re: Pivot Table expression

niclaz79 — Fri, 10 Aug 2018 10:34:11 GMT

Ok, try the following

Avg(Sum(TOTAL <MF> A)/Sum(TOTAL <MF> B))

Re: Pivot Table expression

sunny_talwar — Fri, 10 Aug 2018 11:07:40 GMT

Would you be able to share some raw data or a sample app?

Re: Pivot Table expression

tomcatxx — Mon, 13 Aug 2018 08:58:57 GMT

Yes, I made a test.

Re: Pivot Table expression

BalaBhaskar_Qlik — Mon, 13 Aug 2018 11:59:54 GMT

May be like this:

=Sum(Sales) / Count(DISTINCT Date)

Re: Pivot Table expression

sunny_talwar — Tue, 14 Aug 2018 19:48:18 GMT

Where is the test file? can you share it with us?

Re: Pivot Table expression

neelamsaroha157 — Tue, 14 Aug 2018 20:20:11 GMT

You can use pick and match to get the one column for the avg and target instead of repeating it for everyday. And as Sunny said, it would be easier to understand once you provide the sample data.

Re: Pivot Table expression

tomcatxx — Wed, 15 Aug 2018 06:20:46 GMT

Oh, I'm sorry. It's here. Pivot Avg() .Help!

Re: Pivot Table expression

tomcatxx — Wed, 15 Aug 2018 06:23:23 GMT

Thank you. It's a test. Pivot Avg() .Help!