Count history

[martin_caruso]

Good Morning,

I am developing a board in which I need to count the amount of COD_ID based on some conditions: when the OK field is equal to 1 and when the STATE field is different from RES. It is essential that the accountant take into account the historical: that is to say that it goes from a date to the past.

Next, I leave the table with which I am working. Also, to be clearer in what I need, I leave the result that the meter should give according to the filter that is applied:

• If FY = 2017 and MONTH = ene, then COUNT(COD_ID) = 3. Examples: 1A2B, 3C4D, 9I0J.
• If FY = 2017 and MONTH = oct, then COUNT(COD_ID) = 1. Examples: 3C4D.
• If FY = 2018 and MONTH = nov, then COUNT(COD_ID) = 2. Examples: 7G8H, 9I0J.
• If FY = 2016 and MONTH = may, then COUNT(COD_ID) = 4. Examples: 1A2B, 3C4D, 7G8H, 9I0J.

I'm waiting for the help you can give me.

Thank you very much.

Regards,

Martin.

[mdmukramali]

Hi,

as per my understanding,

I hope you will keep Year and Month as filters in the dashboard.

so no need to include Year and Month in the set analysis.

Can you try below Expression.

COUNT({<OK={1},STATE-={'RES'}>}COD_ID)

[martin_caruso]

Thank you Mohammed for your answer.

I have tried with the expression that you tell me, but it brings me more cases because for example if I select in the filters FY = 2017 and MONTH = ago, the result of the count should give equal to 0, but I get 1 since at some point fulfilled the condition of OK = {1} and STATE - = {'RES'}. Also, it does not take into account the historical.

The database is much bigger than the one you attach.