How to use Pick Match in Pivot table

[vsap2000]

Hello Guys,

I am trying to use following code in a Pivot Table, but it shows values for only first item in valuelist (Total Item Made) for others it doesn't give any values(Total Item Reject it shows 0 and Reject % it shows -)

Pick(Match(ValueList( 'Total Item Made',
                                              'Total Item Reject',
                                              'Reject %'),
                                             'Total Item Made',
                                             'Total Item Reject',
                                             'Reject %'),

            Count(Distinct(ItemKey)),

            Count(Aggr(Count(Distinct({<[Reject Flag] = {'Y'}>}ItemKey)),ItemDeliv_Month,ItemKey)),

           Count(Aggr(Count(Distinct({<[Reject Flag] = {'Y'}>}ItemKey)),ItemDeliv_Month,ItemKey))/
           Count(Distinct(ItemKey))
)

If I use this expression in a Pivot Table it shows value as shown

Thanks in advance

-V

[JordyWegman]

Hi V,

Did you try the calculations without the pivot? Do they work?

Looking at the outcome, it seems like this calculations is not working:

Count(Aggr(Count(Distinct({<[Reject Flag] = {'Y'}>}ItemKey)),ItemDeliv_Month,ItemKey))

Because when it fails, it will give you 0.

But then dividing something with 0, will give a -.

As I'm looking at your calculation, it seems that you have used Distinct not correctly. Distinct is put for instance after the count, but without brackets. So like:

Count( Distinct People )

not

Count( Distinct( People) ) 

So try this calculation:

Count(
    Aggr(
      Count(Distinct{<[Reject Flag] = {'Y'}>}ItemKey)
    , ItemDeliv_Month , ItemKey )
)

Jordy

Climber

[vinieme12]

Pick(Match(  Valuelist()   will not work since the Pick(Match( will not aggregate on the Valuelist() dimension

You need to use If(Valuelist() ='SomeValue' ,  

If you still want to use Pick(Match ( ))      combination, then you need to create a dimension during data load

example

Load Dual(Dim,so_Dim) as PivotDim Inline [

Dim,so_Dim

Total Item Made,1
Total Item Reject,2
Reject %,3
Total Item Made,4
Total Item Reject,5
Reject %,6

];

Then use PivotDim as Dimension in Chart

and replace measure as below

=Pick(PivotDim ,

            Count(Distinct(ItemKey)),

            Count(Aggr(Count(Distinct({<[Reject Flag] = {'Y'}>}ItemKey)),ItemDeliv_Month,ItemKey)),

           Count(Aggr(Count(Distinct({<[Reject Flag] = {'Y'}>}ItemKey)),ItemDeliv_Month,ItemKey))/
           Count(Distinct(ItemKey))
)

[vinieme12]

Pick(Match(  Valuelist()   will not work since the Pick(Match( will not aggregate on the Valuelist() dimension

You need to use an if() block as below

=If(Valuelist('a','b') ='a' ,  expressionfor 'a'

,If(Valuelist('a','b') ='b' ,  expressionfor 'b' ) )

If you still want to use Pick(), then you need to create a dimension during data load

example

Load Dual(Dim,so_Dim) as PivotDim Inline [

Dim,so_Dim

Total Item Made,1
Total Item Reject,2
Reject %,3
Total Item Made,4
Total Item Reject,5
Reject %,6

];

Then use PivotDim as Dimension in Chart

and replace measure as below

=Pick(PivotDim ,

            Count(Distinct(ItemKey)),

            Count(Aggr(Count(Distinct({<[Reject Flag] = {'Y'}>}ItemKey)),ItemDeliv_Month,ItemKey)),

           Count(Aggr(Count(Distinct({<[Reject Flag] = {'Y'}>}ItemKey)),ItemDeliv_Month,ItemKey))/
           Count(Distinct(ItemKey))
)

[vsap2000]

Thanks for reply, doesn't work.

[vsap2000]

Thanks for the reply, if I use if statement I am getting same output showing values for first if condition, others are not shown

Valuelist	Total	Jan-2023	Dec-2022	Nov-2022	Oct-2022	Sep-2022	Aug-2022
Total Item Made	770	15	220	130	95	209	101
Total Item Rejected	0	0	0	0	0	0	0
Reject %	-	-	-	-	-	-	-

If I use load script I get as shown below:

Valuelist	Total	Jan-2023	Dec-2022	Nov-2022	Oct-2022	Sep-2022	Aug-2022
Total Item Made	770	15	220	130	95	209	101
Total Item Rejected	0	0	0	0	0	0	0
Reject %	-	-	-	-	-	-	-
Total Item Made	-	-	-	-	-	-	-
Total Item Rejected	-	-	-	-	-	-	-
Reject %	-	-	-	-	-	-	-

What I saw my all expression works if used in a KPI, and in Valuelist if it's without Aggr function. But for first item in Valuelist Aggr function works but other 2 it doesn't work as you can see from above output

[vsap2000]

Thanks for reply, I tried both the ways but for both getting same result what I was getting before.

For If statement below is the result:

Valuelist	Total	Jan-2023	Dec-2022	Nov-2022	Oct-2022	Sep-2022	Aug-2022
Total Item Made	770	15	220	130	95	209	101
Total Item Rejected	0	0	0	0	0	0	0
Reject %	-	-	-	-	-	-	-

for load script:

Valuelist	Total	Jan-2023	Dec-2022	Nov-2022	Oct-2022	Sep-2022	Aug-2022
Total Item Made	770	15	220	130	95	209	101
Total Item Rejected	0	0	0	0	0	0	0
Reject %	-	-	-	-	-	-	-
Total Item Made	-	-	-	-	-	-	-
Total Item Rejected	-	-	-	-	-	-	-
Reject %	-	-	-	-	-	-	-

When I checked further, all expression works if used in KPI and in table also if there are no Aggr function used.

If Aggr function used in pivot or regular table it works only for first item of the valuelist.

Thanks.

-V

[vinieme12]

You don't need aggr() at all

=Pick(PivotDim ,

            Count(Distinct ItemKey)

           , Count({<[Reject Flag] = {'Y'}>} Distinct ItemKey)

          , Count({<[Reject Flag] = {'Y'}>} Distinct ItemKey) / Count(Distinct ItemKey)
)