topic Re: Pivot table in QlikView

Pivot table

Wed, 18 Feb 2015 16:55:43 GMT

I have made a table which returns a -1 or 0 depending on whether the criteria set in the expression have been met. The expression is

(Today()-Eligibility Data>730 AND (Today()-FIT_Meter Date)>730 OR (Today()-Eligibility Date)>730 AND IsNull(Meter Data).

It provides a list of all the IDs and whether they meet the expression test or not, along with which company they are registered with.

E.g.

Header 1	Header 2	Header 3	Header 4	Header 6
ID	Company	Eligibility Data	Meter Date	Criteria Met
A1	A	07/04/2010	12/09/2013	0
A2	B	07/04/2010	12/09/2013	0
A3	C	07/04/2010	-	1
A4	A	07/04/2010	-	1
A5	B	07/04/2010	12/09/2013	0
A6	C	07/04/2010	01/04/2011	1
A7	A	07/04/2010	12/09/2013	0

I have tried to make a pivot table which has a row for each company and the number of IDs which meet the criteria and the number that don't

e.g.

Header 1	Header 2	Header 3
Company	Met	Not Met
A	2	1
B	0	2
C	2	0

Is there a way to do this?

Any help would be appreciated.

Thanks,

iain

Re: Pivot table

Wed, 18 Feb 2015 17:42:41 GMT

There could be many way to achieve this,

a pivot table example is attached.

pivot_qv.qvw

Re: Pivot table

Thu, 19 Feb 2015 09:40:52 GMT

Thanks for that.

It works, but is not set up in the same way as your table. It has the company on the left then 'Met' and 'Not Met' in rows beside it rather than separate columns.

Also I have a problem with the way the data I have is where each installation owned by a company has two or more meters associated with it which results in the totals being more than doubled. I usually do a set analysis to only count one meter type to resolve this but it does not seem possible in this example.

Any help would be appreciated.