COUNT - a sorted table of a conditioned count

[dpmbrouwers]

Hi Guys,

After my previous hick-up with asking questions here, I’ve got a new one. This time a real one. My challenge is as follows:

• I have a table of production orders requested on a daily basis.
• I also have a table hat describes the BOM per order.
• Finally I have a table with the nr. of items in stock per equipment.

I have created a graph that shows the number of requested and available items per equipment. Whenever I select a day I can scroll through this graph to find the equipments for which demand was higher than supply. For the requested items I’ve created a master item called “Aangevraagd Artikel” and for the available nr of items I’ve created a master item called “Beschikbare Equipments”

The graph is attached and the legend is as follows: The blue column represent the number of requested items, the orange line represents the nr. of available items.

Now I would like to have it the other way around in order to identify the equipments with supply problems easier.

I would like to have a sorted  table that shows the number of times in which the demand for an equipment was higher than the amount of items available based on the production orders on a given day.

I’ve come as far as:

Count({$<[Aangevraagd Artikel]={">$(=Beschikbare Equipments)"}>}[Begindatum Verrichting_p.autoCalendar.Date])

But the result is not matching my expectations. Any help is really appreciated!

Thanks in advance

[rachel_delany]

I would look at using the aggr function.

You could aggregate the total requests and the available by product, and then aggr the difference for each day.

Let me know if you need any further help using the function.

[dpmbrouwers]

Hi Rachel,

Thanks your for your help, I've had a look at the aggr function, but cannot get my head around it yet. Another nudge in the right direction would be reaaly appreciated.

Kind regards

[thevingo]

Hi

Goto chart Properties>Sort tab >> select your dimension >> select expression on the right and then try

aggr(sum([Aangevraagd Artikel] - [Beschikbare Equipments]), Dim1, Dim2...)

I've never tried it, but may work.

Also may experts help @stalwar1

[sunny_talwar]

I am not completely sure I understand the requirement well enough to propose a solution.... would it be possible for dpmbrouwers to provide a sample app to see what they have

[brunobertels]

Hi

May be this

count(aggr(

     Count({$<[Aangevraagd Artikel]={">$(=Beschikbare Equipments)"}>}[Aangevraagd Artikel]),[Begindatum Verrichting_p.autoCalendar.Date]))

[dpmbrouwers]

To all,

Thanks for all the replies, I had the luxury of a long weekend, so I haven't been able to try your suggestions yet. I'm going to try them and let you how it worked out.

[dpmbrouwers]

I have tried Bruno’s suggestion:

I’ve put the statement in a table. When I select a specific equipment, it shows me the info I would like to know: How many times demand was higher than supply. I’ve done a check with a pivot table in excel and that shows the same info.

However, as long as no equipment is selected, but only a period (e.g. 2018) I have no clue what the table is saying. I was actually looking for a table that shows the equipments sorted by the ones with the most issues in supply.

But before I go bothering you all with any further noob-questions, I have found something else in my app, that I want to sort out. This might impact the solution to my original question, so it needs to be sorted first.

So, for now, thanks for your help. As soon as I sorted the issue. I’ll let you know if I need any further help.