Score breakouts

[Spartan27215]

I have people who have a scores called PDC a person can have many of these throughout time I need to be able to break them out as follows

1. Count of people who have an average PDC score >= 80%, Between 50% and 80%, and less than 50%, year over year.

2. Sales $ for people broken out in the above 3 categories, year over year.

The PDC table contains a person identifier, a PDC score and a date.

The Sales table contains a person identifier a sales amount and a date.

[vinieme12]

can you post some sample data?

[Spartan27215]

Not hard to make up some sample data here:
PDC Scores			Sales Dollars
PersonId	Score	Date	PersonId	Amt	Date
1	0.79	4/1/2022	1	40	4/1/2022
2	0.45	4/2/2022	2	99	4/2/2022
3	0.25	1/1/2022	3	66	1/1/2022
4	0.67	2/28/2022	4	44	2/28/2022
5	0.88	3/20/2022	5	33	3/20/2022
6	1	5/2/2022	6	22	5/2/2022
1	0.9	6/2/2022	1	66	6/2/2022
2	0.89	7/8/2022	2	77	7/8/2022
4	0.55	9/1/2022	4	88	9/1/2022
Outcomes
PDC Group	Count	Sales
>= 0.80	3	161
>=0.50  < 0.80	2	308
< 0.50	1	66

[vinieme12]

As below

Load

PersonID,Score,Date

,if(Score<0.5,'<0.5',if(Score<0.8,'>=0.50<0.8','>=0.80')) as PDCGroup

From PdcScoresDataset;

[Spartan27215]

So your saying that the only way to solve this is in the LOAD process? If so that is a problem. user Variables are used to control the grouping.

[vinieme12]

you can create a calculated dimension in the chart as well

=Aggr(if(Score<0.5,'<0.5',if(Score<0.8,'>=0.50<0.8','>=0.80')),PersonID,Score))

[Spartan27215]

Please explain how this would apply, what would be the relationship between this dimension and the rest of the model?

[vinieme12]

based on above two tables

Use P_ID field to only aggregate on PDCScore table values

=Aggr(if(Score<0.5,'<0.5',if(Score<0.8,'>=0.50<0.8','>=0.80')),P_ID,Score))

PDCScores:
Load 
PersonID,PersonID as P_ID,Score,Date
From XyzSource;

SalesDollar:
PersonID,Amt,Date
From AbcSource;

[Spartan27215]

Still makes not sense to me the output of the Aggr will only yield a Text Expression. how that relates back to segregating the data properly is beyond me. I guess I'm just too stupid to lear3n this stuff.