Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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))
)
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))
)
Thanks for reply, doesn't work.
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
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
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)
)