Need help in Formula in Calculated Dimension and Filter

[Anonymous]

Hello!  Pls. help me with my formula

I'm trying to filter and create a new column through calculated dimension  ... a 'material' field with a vertical dimension WeekDate  like  Past Due, Week1, Week2 unitl Week27+ with its corresponding % values ('coverage' expression)... I'd like to create an IF statement  that

1.   In my pivot table,  I'd like to filter only the Material  and show only those which 'WeekDate'  field  from Week1 to W9 that has more than 2  values of less than 80%?...  This means if values that  has less than 80% from  Week1 to 9 will be excluded from the report....My WeekDate ranges from Past Due, Week1, Week2 until Week30+

2.  After the 1st condition met, create a new column (calculated dimension) to mark as  "Red"  if  at least 1 below 80% falls from W1 to W6, IF the below 3 80% below  falls from W7-9, then "Yellow'...

Hoping for your help on this...

[Jason_Michaelides]

It's always helpful to post examples or sample data. Even just in Excel - "here's my data and here's the table I want to get to".

Can you provide more information like that?

[Anonymous]

Hi!  Jason,

Pls. see attached...I hope this will help.

Rgds

[Jason_Michaelides]

What's your current expression for calculating the % Coverage?

[Anonymous]

Here you go :

If((if(Index(concat(DISTINCT [Key Figures], '|'), 'CM Demand'), RangeSum(Before(total [CM Demand],0, ColumnNo(total)))

, if(Index(concat(DISTINCT [Key Figures], '|'), 'Cisco Demand'), RangeSum(Before(total [Cisco Demand],0, ColumnNo(total))), 0))) = 0, 1,

([Cum Supply (Confirmed)])

/

(if(Index(concat(DISTINCT [Key Figures], '|'), 'CM Demand'), RangeSum(Before(total [CM Demand],0, ColumnNo(total)))

, if(Index(concat(DISTINCT [Key Figures], '|'), 'Cisco Demand'), RangeSum(Before(total [Cisco Demand],0, ColumnNo(total))), 0))))

 

Anyway, I'm sending you the actual qvw for better understanding...the coverage expression I was referring to is the 'Supply Coverage (Confirmed)'...The SD Review tab is supposed to be my filtered report from the Supply Demand Coverage tab...

Tks.