5 Replies Latest reply on Feb 23, 2016 8:41 AM by Semanti Mandal

# How to calculate best/ worst case scenario

Hi

I am trying to come up with a best case/ worst case scenario depending on the daily  refresh status of the data.  I have a simple view with workbook name, connection name and the refresh status. I have categorized the refresh statuses as  "1 " and " 5" .

1 = On time Refresh and 5= Late Refresh.

In my data a workbook is using several connections. The refresh status is based on a calculated field.

I am trying to come up with a calculated field which will count the no . of 1's or 5's / workbook and display the best / worst depending on the max. count of 1's or 5's i.e

if count of refresh status =1 > count of refresh status =5 then "Best" else " Worst".

Is this possible? because argument to count is already an aggregated field.

Here is a snapshot of how it looks. So as per this snapshot the status would be "worst". Any suggestions?

• ###### 1. Re: How to calculate best/ worst case scenario

Hey Semanti,

If I am following your goal correctly, it sounds like you need a LoD calculation.

You are going for the whole dataset, so something like this maybe?

Count of 1's:
{SUM(IF [Refresh Status] = 1 THEN 1 END)}

Count of 5's:

{SUM(IF [Refresh Status] = 5 THEN 1 END)}

• ###### 2. Re: How to calculate best/ worst case scenario

Hi Derrick,

Yes , you are right!

I tried something similar  this but it says that

" argument to sum  is already an aggregation and can't be further aggregated".

• ###### 3. Re: How to calculate best/ worst case scenario

Ah, so "Refresh Status" is aggregated? If so, then you can drop the SUM() part and just do something like this:

{IF [Refresh Status] = 5 THEN [My_Aggregated_5_Field] END}

You can also nest LoD calculations if you need to to achieve your goals.

1 of 1 people found this helpful
• ###### 4. Re: How to calculate best/ worst case scenario

Hi Derrick,

While using  LOD , I am getting another error which says that all datasources in LOD must come from same datasources. The [Refresh Status ] field is a calculated field based on two data sources. So that's why LOD's are not working.

Any possible way around to achieve this?

Thanks !

• ###### 5. Re: How to calculate best/ worst case scenario

Hi Derrick,

Since I'm using version 9 , I can't use a LOD expression on a secondary data source via blending. I have to figure out a way around.

But thanks for your help !