Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jamielim
New 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
MVP
MVP

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

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

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

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

Community Browser