cases per assets

[#1]

Hello experts,

I need your help to solve a logic for cases per asstes over time.

I have two separate dimensions: Cases and Assets

An account has more than once cases and an account has more than one assets.

Case module has all case details including case creation date, similarly Asset module has all details of assets including asset install date.

I want to create a chart something like shown below: for cases per assets.

The main logic in order to achieve this chart is: normalized view of cases per assets meaning for a given time frame let's say Q3 2015, cases created during that month divided by all the assets installed till Q3 2015.

For example,

I have cases count like below:

Q1 2015 --> 20

Q2 2015 --> 30

Q3 2015 --> 10

Q4 2015 --> 40

And Assets count like below:

Q1 2015 --> 40

Q2 2015 --> 50

Q3 2015 --> 20

Q4 2015 --> 30

So my logic for the chart will be below:

Q1 2015 --> 20/40

Q2 2015 --> 30/(40+50) --> 30/90

Q3 2015 --> 10/(40+50+20) --> 10/110

Q4 2015 --> 40/(40+50+20+30) --> 40/140

So my numerator is fixed for that time period and my denominator is cumulative value till that time period.

Please help my find a solution for this. Any help is appreciated.

Thank you,

Parth Shah

[arulsettu]

may be this

count(cases)/Above(count(Assets ))

[sunny_talwar]

More like this:

Count(cases)/RangeSum(Above(Count(cases), 0, RowNo()))

[#4]

Thank you both Arul and Sunny. I appreciate your help.

But with this approach, if I try to make any selections, denominator values change. I.E. if I only select Q3 and Q4 2015, than instead of seeing Q3 2015 --> 10/110, I saw Q3 2015 --> 10/20 and for Q4 2015 instead of  seeing 40/140, I saw Q4 2015 --> 40/50.

I would like to preserve the values of denominator with or without any selection.

Hope this helps.

Thanks again for your help,

Parth Shah

[sunny_talwar]

What about this:

Count(cases)/RangeSum(Above(Count({1} cases), 0, RowNo()))

or this where you list all the fields you want to be ignored:

Count(cases)/RangeSum(Above(Count({<QuarterYear, Year, Month, Date, Quarter, MonthYear>} cases), 0, RowNo()))