topic Re: Filter the Materials Based on the Week's Value in QlikView

Filter the Materials Based on the Week's Value

Anonymous — Tue, 25 Feb 2014 19:41:19 GMT

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.

Re: Filter the Materials Based on the Week's Value

vgutkovsky — Tue, 25 Feb 2014 20:11:48 GMT

Take a look at the attached.

Regards,

Vlad

Re: Filter the Materials Based on the Week's Value

Anonymous — Tue, 25 Feb 2014 23:38:28 GMT

Hi! Vlad,

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

Tks...

Re: Filter the Materials Based on the Week's Value

vgutkovsky — Wed, 26 Feb 2014 13:59:15 GMT

Exactly, but the above is the general idea.

Re: Re: Filter the Materials Based on the Week's Value

Anonymous — Wed, 26 Feb 2014 21:26:03 GMT

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.

Re: Filter the Materials Based on the Week's Value

vgutkovsky — Wed, 26 Feb 2014 23:17:50 GMT

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

Re: Filter the Materials Based on the Week's Value

Anonymous — Wed, 26 Feb 2014 23:43:39 GMT

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.

Re: Re: Filter the Materials Based on the Week's Value

vgutkovsky — Tue, 04 Mar 2014 20:51:14 GMT

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

Re: Filter the Materials Based on the Week's Value

vgutkovsky — Thu, 06 Mar 2014 19:03:20 GMT

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

Thanks,

Vlad

Re: Filter the Materials Based on the Week's Value

Anonymous — Thu, 06 Mar 2014 19:08:06 GMT

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

Re: Filter the Materials Based on the Week's Value

Anonymous — Sat, 08 Mar 2014 00:42:23 GMT

Vlad,

Thank you for your help...all are fine...just noticed that some % in Supply Coverage in the first few weeks became "-".. I'll try to figure out the formula again.

Thanks much...