Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
can you post some sample data?
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 |
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;
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.
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))
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;
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.