Counting objects in a pivot table

[stekol61]

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?

[sunny_talwar]

What is the number you are expecting to see?

[stekol61]

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

[sunny_talwar]

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])

[stekol61]

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)"

[sunny_talwar]

- 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.

[stekol61]

Hi1

The pivot is included in my previous ticket:

"Visual Cues in Pivot with different limits"

[sunny_talwar]

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

[stekol61]

How can I upload  the file to an ongoing ticket?

[sunny_talwar]

Uploading a Sample