en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Jesper Bøgekvist  
#1 Posted : Tuesday, October 20, 2015 10:08:33 AM(UTC)
Jesper Bøgekvist
Rank: Member

Groups: extranet\Forum
Joined: 7/7/2015(UTC)
Posts: 9

Hi all

I have a list with social security numbers where they are in between 0 and 4 "activities" each day in september.
The problem is that i have to count how many days they are in an activity in september, but they may only count as 1 person each day even though they are in more than 1 activity. The order of priority is as follows "Kap 12" > "Kap 11" > "Kap 10" > "Kontaktforløb". So if a person, on a given date, is both in "Kap 12" and "Kontaktforløb" he should only count as 1 in "Kap 12" and 0 in "Kontaktforløb".

UserPostedImage

For instance the Black arrow points at a person with 3 different activities during the month. The total at the end should say 17, 13 and 0 because every day he is in "Kontaktforløb" he is also in another activity that has a higher priority.

So my question is, how can i count the total that take the order of priority into account?

Edited by user Tuesday, October 20, 2015 10:22:43 AM(UTC)  | Reason: Not specified

Niels Thomsen  
#2 Posted : Tuesday, October 20, 2015 2:06:03 PM(UTC)
Niels Thomsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 4/5/2011(UTC)
Posts: 105

Hi Jesper

I think I have a solution for your problem.

My suggestion is to make a few intermediate calculations to get there.

First I suggest an accumulated count per person. The y-axis holds 2 dimensions – person and acitivities.
The acitivities of a certain person can be referenced as siblings since the all belong to a certain person.

If you make a calculation as a new measure with the syntax sum(0,d1:0(s),m1) you will have an accumulated count of activities per person for each day.

In a demodata example it could look like this:
UserPostedImage

Having this accumulation in place we can now make a decision whether a certain activity in a certain day should count or not.
If the count of a certain activity a certain day is > 0 and the accumulated count of activities for that certain person that certain day = 1 it should count - otherwise it shouldn't count.

So e.g. in your x-table an activity should only count if the accumulated count is 1 – if any other activity is registered it should not count.

That’s another calculation as a new measure with this syntax: if sum(0,0,m1) = 1 and sum(0,0,m2) = 1 then 1 else 0
In the demodata example it comes out like this:
UserPostedImage

Finally a calculated column with the syntax sum(all,0,m3)
This will give this result:
UserPostedImage

I suppose you will then go to Properties and Visibity to hide all the intermediates.

Edited by user Tuesday, October 20, 2015 2:10:49 PM(UTC)  | Reason: Not specified

Hope it makes sense.

Best Regards
Niels Thomsen

https://dk.linkedin.com/in/ncthomsen


Jesper Bøgekvist  
#3 Posted : Thursday, October 22, 2015 11:10:30 AM(UTC)
Jesper Bøgekvist
Rank: Member

Groups: extranet\Forum
Joined: 7/7/2015(UTC)
Posts: 9

It makes perfect sense!! Just tried it on my own data, and the result is stunning!

Thanks a lot Niels!
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error