Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
keithblythe
Contributor
Contributor

Sum based on min and max date

Hello, I have a data set like the following:

CustomerMaterialPriceOrder DateShip DateQuantity
XYZ11117.956/30/20207/15/2020100
XYZ11117.956/30/20207/22/2020200
XYZ11117.956/30/20207/29/2020100
XYZ11117.956/30/20208/5/2020300
XYZ11117.957/6/20207/15/2020100
XYZ11117.957/6/20207/22/2020400
XYZ11117.957/6/20207/29/2020100
XYZ11117.957/6/20208/5/2020300
XYZ11117.957/13/20207/15/20200
XYZ11117.957/13/20207/22/2020200
XYZ11117.957/13/20207/29/2020100
XYZ11117.957/13/20208/5/2020500

 

I want to show as a KPI a summary of the quantity fields by min order date and max order date.  Show I would expect the following:

Order Date 6/30/2020 - Quantity = 700

Order Date 7/13/2020 - Quantity = 800

Any assistance with this calculation would be appreciated.

1 Solution

Accepted Solutions
dplr-rn
Partner - Master III
Partner - Master III

sorry make $(= instead $(

 

for min date Sum( {<Date={'$(=Min(Date))'}>}  Quantity)

for max date Sum( {<Date={'$(=Max(Date))'}>}  Quantity)

View solution in original post

6 Replies
dplr-rn
Partner - Master III
Partner - Master III

soemthing like below

for min date Sum( {<Date={'$(Min(Date))'}>}  Quantity)

for max date Sum( {<Date={'$(Max(Date))'}>}  Quantity)

keithblythe
Contributor
Contributor
Author

This returned a sum of all values instead of total by order date.

PrashantSangle

instead of sum() you should min() or max()

min date with max quantity

Max( {<Date={'$(Min(Date))'}>}  Quantity)

and 

max date with min quantity

Min( {<Date={'$(Max(Date))'}>}  Quantity)

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
dplr-rn
Partner - Master III
Partner - Master III

sorry make $(= instead $(

 

for min date Sum( {<Date={'$(=Min(Date))'}>}  Quantity)

for max date Sum( {<Date={'$(=Max(Date))'}>}  Quantity)

keithblythe
Contributor
Contributor
Author

I am still getting all records summarized with the revised set analysis.  If I filter by order date then I get a correct number but if the data is unfiltered then it is summarizing all records.

dplr-rn
Partner - Master III
Partner - Master III

got to data modelviewer check the column type of your date field. maybe something is messed up there.

it should have $numeric $date tags

below is a screenshot of an example in my local of a similar expression.

Capture.PNG