Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ZimaBlue
Creator
Creator

sum for min date

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

Labels (1)
1 Solution

Accepted Solutions
ZimaBlue
Creator
Creator
Author

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;

 

View solution in original post

6 Replies
edwin
Master II
Master II

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

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

edwin
Master II
Master II

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

ZimaBlue
Creator
Creator
Author

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?

ZimaBlue
Creator
Creator
Author

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;

 

vinieme12
Champion III
Champion III

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)

 

 

 

 

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
ZimaBlue
Creator
Creator
Author

thank a lot for your reply!