Sum of subset

[Nell]

Can I in Performance Modeler 10.1 count subset? I have CubeA witch have elements User, Attendance (0 or 1) and Time (days). Then I Have CubeB where i would like to know when someone didnt attend for 3 days. My Idea was to make sum of all previous attendances and then compare it with sum of subset 3 days before? It is possible to do this or is there another way how to do it?

[jim wood]

You really need to this via a rule. First you'll need to create a time dimension, then a time lookup cube (so that the calculation will know when to look back to) and then finally create a calculation using the lookup.

[paulsimon]

Hi

Another possibility is to create a consolidation in your time dimension that sums the day and the previous 2 days (use a TI process to generate the consolidations).

Then anyone of these consolidations that has a zero value means that the person did not attend for 3 days.

You will need to check the requirement though, eg if someone did not attend for 5 days, will they get reported on Wednesday, Thursday and Friday? However, I guess that you are looking for more of an early alert, ie some action will be taken as soon as someone does not attend for 3 days?

Regards

Paul Simon

[Nell]

Hi am doing this via rules in the end. But I have one specific problem. I have one Dimension "Work" with attribute "Sum" and "Per_Day" and Dimension "Time" which have hierarchy Years => Quarters => Months => Weeks. So I would like to sum values from Per_Day for every week with previous one and save it into Sum(Example down). I would like to do with help of DIMIX and DIMIN operations. But problem is with indexes of weeks. Because first week has index 4 and not 1 as I expected so I cant return previous element because it is Month not week. Obvious fix is populate Time dimension with weeks only so I will have nice index row from 1 to N. Is it possible to use this approach with Time hierarchy, or I need to do something else?

Per_Day
Week1 = 1
Week2 = 3
Week3 = 2

Sum
Week1 = 1
Week2 = 4
Week3 = 6

[Wim Gielis]

I would like to do with help of DIMIX and DIMIN operations. But problem is with indexes of weeks. Because first week has index 4 and not 1 as I expected so I cant return previous element because it is Month not week. Obvious fix is populate Time dimension with weeks only so I will have nice index row from 1 to N. Is it possible to use this approach with Time hierarchy, or I need to do something else?

Hi,

By all means, please do not rely on indexes in rules like this. Sooner or later your rule will break because the indexes change.
Use attributes and rely on them, rather than indexes.

[Nell]

How do you mean by use attributes? Can you give me something to start from? Is there any function that give me next element or previous?

[Steve Rowe]

You need to create, populate and manage your own Prior / Next attributes for dimensions you need to navigate along. This is very common for time dimensions.

As Wim states, any function that has an index in it should be avoided as the index can not be relied upon in the long term.

Cheers,