Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Spartan27215
Partner - Contributor III
Partner - Contributor III

Score breakouts

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.

Labels (1)
  • SaaS

8 Replies
vinieme12
Champion III
Champion III

can you post some sample data?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Spartan27215
Partner - Contributor III
Partner - Contributor III
Author

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
Champion III
Champion III

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;

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Spartan27215
Partner - Contributor III
Partner - Contributor III
Author

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
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Spartan27215
Partner - Contributor III
Partner - Contributor III
Author

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

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;

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Spartan27215
Partner - Contributor III
Partner - Contributor III
Author

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.