topic Different calculations on different rows in one table (straight or Pivot) in QlikView

Different calculations on different rows in one table (straight or Pivot)

Wed, 16 Mar 2011 08:54:57 GMT

Hello all I have the next question.

In a QV application I have diiferent KPI's on Different sheets. My client wants one sheet with al those KPI's as start page. This will look like this (simplified);

I have the problem that i want to use different formulas for the same expression in QV. Anybody a hint?

Thanks in advance!

Different calculations on different rows in one table (straight or Pivot)

matt_crowther — Wed, 16 Mar 2011 08:59:37 GMT

A simple method would be to contain each formula within an 'if' based either on the dimension value or the rowno() value.

eg: if(rowno()=1,sum(sales),if(rowno()=2,count(Customers),sum(Sales)/count(Customers)))

Hope that helps,

Matt - Visual Analytics Ltd

Different calculations on different rows in one table (straight or Pivot)

Wed, 16 Mar 2011 09:27:16 GMT

Found a solution;

Made a not linked tables with the KPI's, used one of this fields as dimension in the table. And then use an if function to calculate;

If (KPI_nr='1',

COUNT(<Period={$(=maxstring[PIT_Year]-1)}>X),

if(KPI_nr='2',

SUM(<Period={$(=maxstring[PIT_Year]-1)}>Y),

If(KPI_nr='3',

MAX(<Period={$(=maxstring[PIT_Year]-1)}>Y)

)

Different calculations on different rows in one table (straight or Pivot)

Wed, 16 Mar 2011 09:30:46 GMT

Thanks Matt!!