topic Re: Pivot Avg() in QlikView

Pivot Avg() .Help!

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

Hi, there. I have a question about pivot table. I need a pivot table like that.

But I don't know how to write expression to get the last 2 columns. I made a test .qvw for u. Avg is average % value of days for MF. Target is a field which u can find in loaded table.

Help! Thank you very much.

regards

Re: Pivot Avg()

qliksus — Mon, 13 Aug 2018 09:54:31 GMT

There are many ways of doing it a simple can be with adding a dummy dimension and using the below expression

Dim1 : ' '

Dim2 : Region

Exp : If ( isnull(columnno()) , Give your target expression here ,

Avegrage expression here )

Re: Pivot Avg()

tomcatxx — Mon, 13 Aug 2018 10:01:16 GMT

Can u upload .qvw? thank u.

Re: Pivot Avg()

BalaBhaskar_Qlik — Mon, 13 Aug 2018 11:23:17 GMT

May be try like this:

=Sum(Aggr(Avg(Day), Minifabrik))/Count(DISTINCT Day Minifabrik)

Re: Pivot Avg()

tomcatxx — Mon, 13 Aug 2018 13:59:55 GMT

It doesn't work. thank u.

Re: Pivot Avg()

Anil_Babu_Samineni — Mon, 13 Aug 2018 15:54:57 GMT

Probably you need to share excel, That would spot. But, Here is the lucky way

Load * Inline [

Dim

];

Dimension

1) MF

2) Pick(Dim, 'Avg', 'Target')

Expression

Pick(Dim, (Sum(A)/Sum(B)), (Avg(A)/Avg(B)), ((Sum(TOTAL A)/Sum(TOTAL B))/(Count(TOTAL <A> A)/Count(TOTAL <B> B))))

Re: Pivot Avg() .Help!

tomcatxx — Mon, 13 Aug 2018 16:29:46 GMT

Thank you for ur post. But it looks like that. There is only average values.

What I want is like that, in which u can see all values. You don't need Excel because u can find it in qvw.

Re: Pivot Avg() .Help!

sunny_talwar — Wed, 15 Aug 2018 11:42:57 GMT

Check attached

Re: Pivot Avg() .Help!

tomcatxx — Wed, 15 Aug 2018 12:14:48 GMT

Thank you very much. It is what I want.