Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
jamielim
Contributor III
Contributor III

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

CompanyItemDateAmount
CO1A1 Jun 2018100
CO1A1 Jun 2018-100
CO1B2 Jun 2018100
CO1B2 Jun 2018-50
CO1C4 Jun 2018100
CO2A5 Jun 2018200

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:

CompanyMin (Date)Sum(Amount)
CO12 Jun 2018150
CO25 Jun 2018200

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!

2 Replies
petter
Partner - Champion III
Partner - Champion III

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
Contributor III
Contributor III
Author

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