topic Re: Score breakouts in App Development

Score breakouts

Spartan27215 — Mon, 19 Sep 2022 18:04:23 GMT

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.

Re: Score breakouts

vinieme12 — Wed, 21 Sep 2022 08:26:59 GMT

can you post some sample data?

Re: Score breakouts

Spartan27215 — Wed, 21 Sep 2022 14:23:59 GMT

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

Re: Score breakouts

vinieme12 — Thu, 22 Sep 2022 01:46:40 GMT

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;

Re: Score breakouts

Spartan27215 — Thu, 22 Sep 2022 13:45:07 GMT

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.

Re: Score breakouts

vinieme12 — Thu, 22 Sep 2022 23:45:14 GMT

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

Re: Score breakouts

Spartan27215 — Mon, 26 Sep 2022 17:51:12 GMT

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

Re: Score breakouts

vinieme12 — Tue, 27 Sep 2022 07:38:57 GMT

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;

Re: Score breakouts

Spartan27215 — Tue, 27 Sep 2022 22:27:03 GMT

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.