topic Re: sum for min date in App Development

sum for min date

ZimaBlue — Thu, 12 May 2022 17:37:29 GMT

Hello!

How to make this very simple action work in qlik sense (table chart)?

I try:

=sum({<payment_date = {"Min([payment_date])"} >} amount)

but it doesnt work. I also tried

=sum({<payment_date = {'$(=Min([payment_date]))'} >} amount)

but it's also useless

Re: sum for min date

edwin — Thu, 12 May 2022 20:30:23 GMT

min results in a number, so you need to convert it to date:

=sum({<payment_date = {'$(=date(Min([payment_date])))'} >} amount)

Re: sum for min date

edwin — Thu, 12 May 2022 20:31:59 GMT

but its not that simple. if this is in a table with payment date as a dimension it will not work as the value of payment date becomes the min. but if this is just a number that stands on its own like a kpi then it could work

Re: sum for min date

ZimaBlue — Fri, 13 May 2022 06:28:10 GMT

Hello!

Why can't I display this in a table? I output as measurements: the user id, the minimum date, and I want to output by this minimum date the corresponding payment amount, which is only one. In this case, how do I get such an array of data?

Re: sum for min date

ZimaBlue — Fri, 13 May 2022 08:22:54 GMT

I loaded it piecemeal through a script. Anyway, thanks a lot for the answer!

I wrote the data upload part with the minimum date, and then used join to attach the corresponding payment amount by date and user id

final:

load
user_id,
timestamp(min(payment_date),'YYYY-MM-DD hh:mm:ss') as min_date

resident ec_users

group By
user_id;

//join
left join (final)
load
amount,
payment_date as min_date,
user_id

Resident ec_users;

Re: sum for min date

vinieme12 — Fri, 13 May 2022 08:27:30 GMT

Doing this in set analysis of the Measure is not possible,

Since each UserID will have a different min(date) and {"Min([payment_date])"} is evaluated once per chart and not once per dimension value ; so this will only return ONE min value

What you can do is use a calculated dimension

Dimension

=UserID

=Aggr(if(payment_date=min(payment_date),date),UserId) <<--uncheck include null values for this dimension

Measure

=sum(amount)

Re: sum for min date

ZimaBlue — Thu, 19 May 2022 13:23:42 GMT

thank a lot for your reply!