pls help on set analysis and aggr function

msmichael — Thu, 18 Oct 2018 18:25:15 GMT

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,

Re: pls help on set analysis and aggr function

achettipalli — Fri, 19 Oct 2018 13:47:02 GMT

Please find the attached.

used Sum(aggr(FirstSortedValue({<Update_date={"<=$(=vDate)"}>} Is_Active,-Update_date),prodId))

// I'm sure you can improve the above code for better performance.

// If its used more often in the dashboard, best suggestion is to use it in LOAD script and generate a column.

More info on FirstSortedValue(): FirstSortedValue()

Good luck!

Avinash

Re: pls help on set analysis and aggr function

msmichael — Fri, 19 Oct 2018 20:21:55 GMT

Hi Avinash,

Thank you so much for your help. It's exactly what I need. I appreciate it!!

Michael

Re: pls help on set analysis and aggr function

achettipalli — Fri, 19 Oct 2018 20:58:01 GMT

I'm glad its helpful.

topic pls help on set analysis and aggr function in QlikView

pls help on set analysis and aggr function

Re: pls help on set analysis and aggr function

Re: pls help on set analysis and aggr function

Re: pls help on set analysis and aggr function