Filter the Materials Based on the Week's Value

[Anonymous]

Hello!  Pls. help me figure out how to filter my Pivotchart  and do color coding based on values... Pls. see my file (in excel) for better understanding...

1.    I'd like to filter only the material and show only those which weekly field  from Week 1 to W9 has more than 2  values of less than 80%?...  This means if values that  has less than 80% from  Week1 o 9 will be excluded from the report.

2.  After the 1st condition met, color the percentage to red if  at least 1 below 80% falls from W1 to W6, IF the below 3 below 80% falls from W7-9, then yellow...

Hoping for your help on this...I already spent days on this how to figure out  my dahboard...

Tks.

[vgutkovsky]

Take a look at the attached.

Regards,

Vlad

[Anonymous]

Hi!  Vlad,

  I  forgot to mention that my week no goes up to W52 ...I guess I need to change the formula?

Tks...

[vgutkovsky]

Exactly, but the above is the general idea.

[Anonymous]

I'm sorry, Vlad ... but I can't get this work while your sample worked...maybe bec. my original qvw is more complicated than my sample.  I'm attaching my original qvw for better understanding...The SD Review is what I'm working on...This is supposed to be the filtered pivottable  from the original tab "SD " ...

Not sure how to fix the expression as there is already an existing expression in the  "Supply Coverage (Confirmed)"  (I, actually inherited this qvw and am still learning)...  Will it work if we put the expression for filtering in the Part # dimension?

For the conditional formating ,  I was thinking of adding a calculated dimension intead of doing a color coding in the % values....like put "Yellow" if the 3 or more below 80% are in Week 7-9...if not, then "Red"...I think this is better so I can do another filtering which ones are more critical "Red" and which are less crtical "Yellow".

Rgds.

[vgutkovsky]

The original business rules that you posted above don't seem to have any relation to this application, so you'll have to be a lot more specific on what exactly you need it to do.

Vlad

[Anonymous]

My bad...Sorry for the confusion...Anyway, I hope I've  made myself clear this time ...

1.   In the SD_Review tab's pivot table,  I'd like to filter only the Part #  (Base Part Number) and show only those which 'Week Date w/ Past'  field  from Week 1 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.

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...

Tks.

[vgutkovsky · Accepted solution]

Sorry for the delayed response. Your formulas in that "Supply and Demand Coverage" chart were extremely complex and I've simplified them a bit. Keep in mind for the future that indenting is very important, or else you can easily get lost in your code. See attached for the solution. The whole thing is driven by the 2nd dimension in the chart, which is suppressed when null. You've probably already figured out that you can't use function before() in a calculated dimension with aggr(), so that's why this calculated dimension has been built the way it has. Finally, your requirements are extremely heavy, so I highly recommend you implement a calculation condition on the chart to prevent it from crunching all data unfiltered (which takes about 90 seconds).

Regards,

Vlad

[vgutkovsky]

Please mark correct answers as Correct (just generally good forum etiquette).

Thanks,

Vlad

[Anonymous]

Hi! Vlad,

Sorry...wasn't able to check and verify yet ...got busy with other stuffs....but will surely  mark (as I always do 🙂 as soon as I tested your sample by tomorrow.

Thanks for reminding ...