Count unique row that match with an inventory date

[maxime66]

Hy Qlikers,

I would like to create an expression that only count the row in yellow in this screenshot. (and 0 for others) for each [num_affaire]

in the exemple i define a date_of_inventory = 31/08/2022 .

i can successfully define what rows can't  be in the inventory (stock =0) , but i can't go further and flag the yellow one.

stock=count(distinct if( floor(tache_date_heures) > date_of_inventory , null(),num_affaire))

if someone can help me 🙂

[Kushal_Chawda]

@maxime66  Is the date of inventory hardcode value? 

[maxime66]

Hello @Kushal_Chawda 

it's a date that can be filter in a indépendant calendar table.

[Kushal_Chawda]

@maxime66  try below expression

=aggr(if( floor(taches_date) > max(total date_of_inventory),0,1),num_affaire,taches_date,status)

[maxime66]

Thank you so much , but something doesen't work . maybe more simple with the exemple below screenshot + qvw app ?!

[Kushal_Chawda]

@maxime66  is this output you want?

[maxime66]

actually it is this one 

[Kushal_Chawda]

@maxime66  what is the logic to get that output?

[maxime66]

We want to know the number of [num_affaire] by [Statut] at different selected inventory_date.

at 31/12/2023 I have 2 [num_affaires] and they are both in a "Stand by" [statut]...

[Kushal_Chawda · Accepted solution]

@maxime66  try below

=count(DISTINCT aggr(if(
date_affaire= max(total <num_affaire>{<date_affaire={"<=$(=max(calendrier.date_dern_jour_mois))"}>}date_affaire),num_affaire),
num_affaire,date_affaire))