MOM count based on change in another column

rajeshshuklars — Fri, 02 Apr 2021 07:51:45 GMT

So, I have gym user data and the dates when the user came in and which trainer took care of the user. There are 4 Trainer groups I have created,

My Data has say 6 Columns:

User - User who used the gym on a date
Gym_Trainer - Gym trainer who helped the user
Trainer_Group - The group to which the trainer is associated with. The trainer can switch the groups.(thats making it difficult for me)
User_Acct_No - Account number of the user
City - City of the user
Date - Date of service for the user

The Trainer_Group: There are 4 possible values for this field
Redcross
PlanetFitness
Golds
Unknown Company

My data in csv format:

User ; Gym_Trainer ; Trainer_Group ; User_Acct_No ; City ; Date
1 ; Raj ; Redcross ; PS0054133301 ; Taunton ; 3/2/2018
2 ; Raj ; Redcross ; GS0054133302 ; Taunton ; 3/4/2018
3 ; Raj ; Redcross ; GS0054133303 ; Taunton ; 3/1/2018
4 ; Raj ; Redcross ; GS0054133304 ; Taunton ; 3/1/2018
5 ; Raj ; Redcross ; GS0054133305 ; Taunton ; 3/1/2018
6 ; Raj ; Redcross ; GS0054133306 ; Taunton ; 3/1/2018
7 ; Raj ; Redcross ; TF0054133307 ; Middleboro ; 3/2/2018
8 ; Raj ; Redcross ; GS0054133308 ; Taunton ; 3/2/2018
9 ; Raj ; Redcross ; GS0054133309 ; Taunton ; 3/3/2018
10 ; Raj ; Redcross ; GS0054133310 ; Taunton ; 3/3/2018
11 ; Raj ; Redcross ; GS0054133311 ; Raynham ; 3/4/2018
12 ; Raj ; Redcross ; GS0054133312 ; Taunton ; 3/4/2018
13 ; Raj ; Redcross ; GS0054133313 ; Taunton ; 3/2/2019
14 ; Raj ; Redcross ; GS0054133314 ; Fall river ; 3/2/2019
15 ; Raj ; PlanetFitness ; GS0054133315 ; East taunton ; 3/3/2019
16 ; Raj ; PlanetFitness ; GS0054133316 ; Taunton ; 3/3/2019
17 ; John ; Golds ; GS0054133317 ; Taunton ; 3/4/2019
18 ; John ; Golds ; RG0054133318 ; Taunton ; 3/5/2019
19 ; John ; Golds ; GS0054133319 ; Taunton ; 3/6/2019
20 ; John ; Golds ; GS0054133320 ; Taunton ; 3/7/2019
21 ; John ; Golds ; GS0054133321 ; Taunton ; 3/8/2019
22 ; John ; Golds ; GS0054133322 ; Taunton ; 3/9/2019
23 ; John ; Golds ; GS0054133323 ; Middleboro ; 3/10/2019
24 ; John ; Golds ; GS0054133324 ; Taunton ; 3/11/2019
25 ; John ; Golds ; GS0054133325 ; Taunton ; 3/12/2019
26 ; Rita ; Unknown Company ; GS0054133326 ; Taunton ; 3/4/2018
27 ; Rita ; Unknown Company ; GS0054133327 ; Raynham ; 3/5/2018
28 ; Rita ; Unknown Company ; GS0054133328 ; Taunton ; 3/1/2019
29 ; Rita ; Unknown Company ; GS0054133329 ; Taunton ; 3/2/2019
30 ; Rita ; Unknown Company ; GS0054133330 ; Fall river ; 3/2/2019
31 ; Rita ; Unknown Company ; GS0054133331 ; East taunton ; 3/3/2019
32 ; Rita ; Unknown Company ; GS0054133332 ; Taunton ; 3/3/2019

Whenever I do MOM user volume analysis (Last years MTD - This years MTD) by each group I am using the formula for measure with the dimension of Trainer_Group-

Count({<ExcludeFutDates = {'1'}>}distinct User_Acct_No)
-
Count({<Date={">=$(=date(monthstart(addyears(max(Date),-1)))) <=$(=date(addyears(max(Date),-1)))"},[Day of Year] = {"<$(=DayNumYear)"}>}distinct User_Acct_No)

There could be duplicates for a user on same day so I am eliminating them by distinct. This works great untill I have a situation of Trainers switching the Trainer group.
e.g March 2019 MTD volume for Trainer group Redcross is - 2
March 2018 MTD volume for Trainer group Redcross is - 12

So 2019 MTD-2018MTD=2012 = -10 which is great and when I choose my dimension as Gym_trainer instead of Gym_Group then the bar chart for Raj
March 2019 MTD - March 2018 MTD = 4-12 = 08 which is great too.

My problem is I want to create a drilled dimension by Gym_Group>>Gym_Trainer anf then show the MOM change and who switched a particulat Gym_Group.

When I drill to Gym_Trainer level for Raj for Redcross I see the
2019 MTD-2018 MTD= 2-12 = -10 which is fine

Now when it is showing the data for the Gym_Group PlanetFitenss thats where the problem arise, I see the value as -
2019 MTD - 2018 MTD = 2 - 0(since raj has no entry/volume for 2018) = 2 My users are finding it difficult to grasp, Raj had no volume for last year for PlanetFitness but still his MOM value shows increase of +2 which is misleading. The MOM calculation should only happen if there was volume last year
for Raj in Trainer_Group PlanetFitness.

I have tried so many things here, but no anvail 😞 I tried P function and intersection but its not working. I don't want to exclude 0 from last years value calculation since it might rliminate legitimate records.

I have 2 problems:
1. For the MOM calculation(2019 MTD- 2019 MTD) how can I only include the Gym_Trainer if he/she was the part of same Trainer_Group?
2. If we can't exclude then a way to highlight that to my users.
3. Any changes I can make to load scripts to track the movement of Trainers across Trainer_Groups?

Please give your valuable inputs. Thanks

topic MOM count based on change in another column in App Development

MOM count based on change in another column