Min date across all Item dimension where sum amount by item is not zero

[jamielim]

Company	Item	Date	Amount
CO1	A	1 Jun 2018	100
CO1	A	1 Jun 2018	-100
CO1	B	2 Jun 2018	100
CO1	B	2 Jun 2018	-50
CO1	C	4 Jun 2018	100
CO2	A	5 Jun 2018	200

I have the above table loaded in my data load script.

Using Qlik Sense, I will like to create a table that gives me the company, the min date for each Company only for those item whose sum of amount is not zero, and the sum of the amount for each company. In the table above, item A for CO1 will be disregarded in the Min date computation as the sum of amount for Item A under CO1 is zero. The result i will like to achieve is:

Company	Min (Date)	Sum(Amount)
CO1	2 Jun 2018	150
CO2	5 Jun 2018	200

Appreciate any advise on this as I cannot seem to get CO1 to reflect min date of 2 June 2018. It always picks up 1 June 2018.

Thanks!

[petter]

You can search for all dates that don't sum up to zero and then find the min value amongst them by using a set expression like this:

Min( {<Date={"=Sum(TOTAL <Company,Date> Amount)<>0"}>} Date)

[jamielim]

Hi petter-s,

Thanks for your reply and yes, that indeed works for the example I gave above.

But when I applied it to my actual data, somehow the date is still incorrect unless, I filter for the CO1 specifically. Any idea what modification I should make or what could be causing the issue here? The date reflected in my output table should not change regardless of the filter on Company dimension.

Regards,

Jamie