Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

tomcatxx
New Contributor II

Pivot Table expression

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

MinifabrikDayWeThFrAvgTarget
MF1%%%%%
MF2%%%%%
MF3%%%%%
MF4%%%%%

I made a table but not finished.

3.PNG

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,

          A,

          B,

          T

From xx.qvd

12 Replies
niclaz79
Contributor III

Re: Pivot Table expression

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

suhaskharat
New Contributor III

Re: Pivot Table expression

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

tomcatxx
New Contributor II

Re: Pivot Table expression

Thank u, but it doesn't work.

no.PNG

tomcatxx
New Contributor II

Re: Pivot Table expression

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.

niclaz79
Contributor III

Re: Pivot Table expression

Ok, try the following

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

Re: Pivot Table expression

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

tomcatxx
New Contributor II

Re: Pivot Table expression

Yes, I made a test.

balabhaskarqlik
Honored Contributor

Re: Pivot Table expression

May be like this:

=Sum(Sales) / Count(DISTINCT Date)

Re: Pivot Table expression

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

Community Browser