topic Re: Sum based on min and max date in App Development

Sum based on min and max date

keithblythe — Fri, 24 Jul 2020 14:58:04 GMT

Hello, I have a data set like the following:

Customer	Material	Price	Order Date	Ship Date	Quantity
XYZ	1111	7.95	6/30/2020	7/15/2020	100
XYZ	1111	7.95	6/30/2020	7/22/2020	200
XYZ	1111	7.95	6/30/2020	7/29/2020	100
XYZ	1111	7.95	6/30/2020	8/5/2020	300
XYZ	1111	7.95	7/6/2020	7/15/2020	100
XYZ	1111	7.95	7/6/2020	7/22/2020	400
XYZ	1111	7.95	7/6/2020	7/29/2020	100
XYZ	1111	7.95	7/6/2020	8/5/2020	300
XYZ	1111	7.95	7/13/2020	7/15/2020	0
XYZ	1111	7.95	7/13/2020	7/22/2020	200
XYZ	1111	7.95	7/13/2020	7/29/2020	100
XYZ	1111	7.95	7/13/2020	8/5/2020	500

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.

Re: Sum based on min and max date

dplr-rn — Fri, 24 Jul 2020 15:13:33 GMT

soemthing like below

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

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

Re: Sum based on min and max date

keithblythe — Fri, 24 Jul 2020 15:26:26 GMT

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

Re: Sum based on min and max date

PrashantSangle — Fri, 24 Jul 2020 15:26:56 GMT

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

Re: Sum based on min and max date

dplr-rn — Fri, 24 Jul 2020 15:37:09 GMT

sorry make $(= instead $(

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

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

Re: Sum based on min and max date

keithblythe — Fri, 24 Jul 2020 16:17:23 GMT

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.

Re: Sum based on min and max date

dplr-rn — Fri, 24 Jul 2020 16:25:39 GMT

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.