Filter pivot table by cell value.

[Frigide]

I like to make a nice OEE dashboard of date from my work.
For that i wan't chart and totals of 24 our only. So how do i filter several pivot tables to 24 ours only?

Maybe to filter a table between two cell. So anyone can choose a begin date?

Is there someone how can make a example. Maybe a nice dashboard to begin with? (totals, average/h, yield, etc)

[dflak]

Sometimes with pivot tables you need helper columns in the source data table. I created one called "In Date Range" and the formula for it is: =AND([@[Datum / tijd]]>=Pivot!$H$6,[@[Datum / tijd]]<=Pivot!$J$6)

I refreshed the pivot table and used this as a report filter.

[Frigide]

This is working very nice!! Is there a youtube or manual how to do this. I have no idea what you did or how yo did it.

[dflak]

No, I do not have a video, but I will eventually have an instruction set. One of my "jobs" at work is to teach other people Excel. I run a lunchtime lesson on a different thing about Excel once a month. Pivot tables will be on the January schedule.

[hansolu]

Hi

dflak just added a new column in your pivot data (sheet PKD). This column returns true or false, depending on the values you have below your slicer.
Then filter the pivot table with this new true/false value.

I was also working on a OEE Dashboard. It took me a while to grab data, transform and use the datamodel to create measures so the dashboard is automtically showing what i want to show.
I used this board but also Youtube for some videos on how to make a dashboard, how the datamodel is working and how measures can be done.

Overall, I guess you need to spend some time reading and learning

Good luck

[dflak]

Thanks for jumping in and explaining. Helper columns are often the solution to difficult pivot table problems.

For example, one of the problems with pivot table filters is that if you have everything selected and a new item appears in the data, then the new item also shows up checked as visible. However if you have ANY item checked off, then when a new item shows up, it appears in the filter checked as NOT visible. For example you may be analyzing failure reason codes for an item and you don't want "Other," so you check it off. Now a new reason code appears. It will NOT show up on the pivot table. In this case you should create a column called "Not Other" and the formula would be =[Reason] <> "Other".

In general, you want these helper columns to evaluate to True / False, but I have used it to group failure reason codes into "Electrical," "Mechanical," etc. by using VLOOKUP against a table that groups them.

[Frigide]

Thanks you two. I got it now! This wil make my live much easier. Is there a a way to see words in a chart? I like to see what the reason of downtime is in or below a production chart.

[dflak]

After you make the chart, right click on it. A plus sign (+) should appear just outside the cart on the upper right. When you click on this icon, you will get a menu of some of the things you can do with the chart. One of them should be data labels. Click on that and data labels will be added. Right click on any of the labels and you will get dialog boxes to format them.

[Frigide]

Is there a way to count how many times a word is in the same time window?
For example how many times the word "Dagelijkse taken" is in the column "reason downtime wasline" in the true time window

[dflak]

Look up the COUNTIFS function.

[Frigide]

Old post i know!
This answer is very helpfull. I use AVERAGEIF and so on.
Is there a iscellif?
When a certain time in combination with a line (lijn) then cell is cell?