community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Calculate stats based on Categories

Meteor

Hello everyone :) 

I have a question, how would it be possible to do something like this, for example I have data like this: 

 

image.png

 

The end result should have data like this: 

 

image.png

 

The calculations work on Category and Subject mainly. 

 

I. If there is ADDED/VERIFIED or UPDATED/VERIFIED, there should be 2 Categories. For example A, B.

  • ID 1 it means that A ADDED 1 and B VERIFIED 1. 
  • ID 2 it means that A UPDATED 1 and B VERIFIED 1. 
  • ID 3 it means that B ADDED 2 and C VERIFIED 2. 
  • and so on. 

II. When we have ACTIVATED or DELETED, only one Category is needed, so ID 5 is good and can be calculated. 

III. When we have 3 Categories or more for example ID 6, all their actions are unknown and are calculated in a separate column (doesn't matter if the subject has ADDED or anything else), So all A, B and C Category will get a point in Unknown column. 

IV. When we have an empty Category for example ID 8, we calculate what was done, but under Unknown. 

V. When we have a good subject but only one Category, for example ID 9, then for C Category it will count as Unknown Action. 

 

Also, all 3 or more (depends on how many folders there will be) tables are needed. Like the second picture, one for the total, the second is for ABC folder and the last one for XYZ folder. 

 

There is no limit how many Categories the data might have, depends on the data, so unfortunately I cannot have a list of Categories ready for the final calculations. 

Same goes for Folders, they can change.

As for the time, sometimes I need to calculate the stats based on a time period. For example only calculate during 2018.09.13 1:00:00 and 2018.09.13 5:00:00. Anything that is out of that time scope should be ignored. 

 

I have written a VBA macro that does these calculations. The macro can adjust to the data that was extracted. But as the there is a lot of data, from different sources, it takes quite a long time to finish everything. I`m trying to figure out a way to make the same calculations using Alteryx. Herd that possibilities here are limitless, but I really not sure where to start from. Was trying to find a way to separate Categories by creating new rows and then calculation, but with no luck in that. 

 

Any help is greatly appreciated and if you will need any more info, let me know, I`ll try to give it :) 

Alteryx Certified Partner
Alteryx Certified Partner

I've put something together to get you started. I wasn't sure how to identify scenarios where there's extra notes vs an additional category assigned that would invalidate the others since it's more than 2. 

 

 

Meteor

Thank you for the yxmd :) It gave me quite a few new things I could try out. 

image.png

This part is quite interesting. But it kind of duplicates the values, like both A and B get ADDED point and VERIFIED point, when it should be A ADDED and B VERIFIED. But I like this idea, need to play around with it more :) 

I was also playing around with dividing the lines based on Categories. 

image.png

Tried using something like this. I was also able to add 4 columns, just in case if there were more then 2 Categories. Then Alteryx got a bit confused when I tried to add values based on the column number, if 1st then ADDED, if 2nd then VERIFIED, if 3rd is present, ignore the rest. 1st and 2nd seems possible and I did get some things that look similar to what I want, but when I try to add something about 3rd, nothings works so far.. 

 

Plus there could be another weird possibility when one like could have bot ADDED/ VERIFIED and UPDATED/VERIFIED at the same time. 

My VBA macro looks at all ADDED first, then UPDATED, and so on. And the table is filled in with the logic: value +1 if found. It takes quite some time, but I still cant find an alternative in Alteryx. Or is there something similar to that and I just haven't found it yet? :) 

Labels