Hi experts,
My case is there are several product managers (A, B, C etc.), each manger manages some products (prodId) by setting the product to be Active/Inactive. A product can be set to active/inactive for many times.
So I have a table to track the history of each product, the table looks like below,
ProdManager | prodId | Is_Active | Update_Date | Comment |
A | 101 | 1 | 1/1/2018 | Product activated |
A | 101 | 0 | 5/1/2018 | Product deactivated |
A | 102 | 0 | 2/1/2017 | … |
A | 102 | 1 | 2/1/2018 | … |
A | 102 | 0 | 3/1/2018 | … |
B | 201 | 1 | 1/15/2018 | … |
B | 201 | 0 | 2/15/2018 | … |
B | 201 | 1 | 3/15/2018 | … |
B | 201 | 0 | 4/15/2018 | … |
B | 201 | 1 | 5/15/2018 | … |
For example, ProdManager A activated ProdId=101 on 1/1/2018, so this prodId keeps in Active state until it's set to be Inactive on 5/1/2018.
Now I want to count how many active reports each manager have as of a date user specified (kept in a vDate variable)
For example, if vDate = 2/28/2018, I should get below table:
ProdManager | Num of Active Products |
A | 2 |
B | 0 (because prodId 201 is not in Active state as of 2/28/2018) |
For manager A, the num of active products as of 2/28/2018 is 2, because prodId = 101 and 102 are both in Active state.
For manager B, the num of active products as of 2/28/2018 is 0, because no active products (201 was set to Inactive on 2/15/2018)
I tried using below formula to for "Num of Active products" column, but it gives me wrong answer on manager B.
=count(
{$<[Is_Active]={1}>}
aggr(max({$<Update_date={"$(='<=' & date(vDate) )"} >} Update_date), ProdManager,prodId)
)
My idea is for each manager and each product, I first find out the records that have the max Update_Date from all records that are earlier than vDate, then I count how many records in this subset have Is_Active = 1.
I attached my qvw for your reference. Could anyone help me get the correct formula for this? I appreciate your help!
Thanks,
