topic Re: Counting objects in a pivot table in QlikView

Counting objects in a pivot table

stekol61 — Tue, 14 Mar 2017 15:42:43 GMT

Hi!

In the encosed document I want to count number of Node Id's for each Object and Tech and for the last week available.

I've tried to use this expresion but If I select several weeks the result will be aggregated

*=Count ({<Tech= {'3'} >}[Node Id])*

How can this be done?

Re: Counting objects in a pivot table

sunny_talwar — Tue, 14 Mar 2017 16:29:52 GMT

What is the number you are expecting to see?

Re: Counting objects in a pivot table

stekol61 — Wed, 15 Mar 2017 08:01:39 GMT

Hi!

Column 6 are the values I want to se for object A for tech 2, 3 and 4.

I always want to see the values for the last week available.In this example week 11

2017	11	A	4	99,37%	12914
2017	11	A	2	99,11%	7634
2017	11	A	3	99,82%	34980

Re: Counting objects in a pivot table

sunny_talwar — Wed, 15 Mar 2017 08:17:08 GMT

Create a new field in the script like this

LOAD *,

YEAR * 100 + WEEK as YearWeek;

and then, try this expression

=Sum({<Tech= {2, 3, 4}, YearWeek = {"$(=Max(YearWeek))"}>}[Node Id])

Re: Counting objects in a pivot table

stekol61 — Wed, 15 Mar 2017 15:12:04 GMT

Hi!

A few things:

- I have YearWeek loaded in the format "2017-11". Does this matter?

- The format of Tech is G2, G3 and G4 not 2,3 and 4. Does this matter?

- I want these new Count-values to be added to the current pivot after "MCDR(4)"

Re: Counting objects in a pivot table

sunny_talwar — Wed, 15 Mar 2017 15:16:40 GMT

- I have YearWeek loaded in the format "2017-11". Does this matter?

As long as its a dual field, it won't matter.... or you can use MaxString(YearWeek) in the set analysis

=Sum({<Tech= {2, 3, 4}, YearWeek = {"$(=MaxString(YearWeek))"}>}[Node Id])

- The format of Tech is G2, G3 and G4 not 2,3 and 4. Does this matter?

Don't think this matter... I guess you can add them with single quotes (not needed, but if they had spaces, then we would have def. needed them)

=Sum({<Tech= {'G2', 'G3', 'G4'}, YearWeek = {"$(=MaxString(YearWeek))"}>}[Node Id])

- I want these new Count-values to be added to the current pivot after "MCDR(4)"

Should be doable, but I don't have your current pivot table, so won't know how you created it.

Re: Counting objects in a pivot table

stekol61 — Wed, 15 Mar 2017 15:23:25 GMT

Hi1

The pivot is included in my previous ticket:

"Visual Cues in Pivot with different limits"

Re: Counting objects in a pivot table

sunny_talwar — Wed, 15 Mar 2017 15:28:52 GMT

I don't see any qvw attached... I won't be able to put together another qvw from your previous thread.... if you can provide a qvw, I might be able to look into it

Re: Counting objects in a pivot table

stekol61 — Wed, 15 Mar 2017 15:35:35 GMT

How can I upload the file to an ongoing ticket?

Re: Counting objects in a pivot table

sunny_talwar — Wed, 15 Mar 2017 15:37:11 GMT

Uploading a Sample

Re: Counting objects in a pivot table

stekol61 — Wed, 15 Mar 2017 15:39:55 GMT

OK.

Here it is

Re: Counting objects in a pivot table

sunny_talwar — Wed, 15 Mar 2017 15:43:18 GMT

Where is Tech in this qvw?

Re: Counting objects in a pivot table

stekol61 — Wed, 15 Mar 2017 15:49:08 GMT

It's incuded in the expressions. e.g. "4 Availability" is showing data for G4

Re: Counting objects in a pivot table

stekol61 — Wed, 15 Mar 2017 15:55:29 GMT

e.g "MCDR (4)" looks like this:

=num(100-((sum({<Tech= {'G4'} >} [Call Fails])+sum({<Tech= {'G4'} >}[Call Drops]))/sum({<Tech= {'G4'} >}[Call Attempts]))*100,'# ##0,00')