topic Re: Variable Pacing using rangesum or sum with set in App Development

Variable Pacing using rangesum or sum with set

Anonymous — Wed, 24 Jan 2018 16:29:15 GMT

Hi all,

I am trying to get a function together to get an idea of how I am doing in terms of getting orders

Sample Data

Date	Date Month	Date Year	Delivery Month	Business Days Remaining in Month	Order Quantity
1/23/2018	1	2018	2	7	10
1/23/2018	1	2018	3	7	5
1/23/2018	1	2018	2	7	16
1/15/2018	1	2018	2	13	20
12/29/2017	12	2017	2	1	10
12/15/2017	12	2017	2	8	5
12/19/2017	12	2017	1	7	50
12/15/2017	12	2017	1	8	10

etc.

The output I would like to see is

With 7 days remaining in January --> how many orders do we have (irregardless of month) and where were we at compared to December at the same time?

Final output desired --> using delivery month of date month +1

Date Year	Date Month	Delivery Month	Business Days	Total Orders
2018	1	2	7	61
2017	12	1	7	60

So far I have tried the following equations --> which work only for current month and not for prior month or on year changes (i.e. looking at January in December)

Sum({<[Delivery Month]={'"$(=vMaxDateMonth+1)"},[Delivery Year]={'$(vMaxDateYear)'},

Date = {'<=$(=vMaxDate)'}>}OrderQuantity)

I have also tried using rangesum but I have not gotten it to work in any meaningful way.

Thank you

Jacob

Re: Variable Pacing using rangesum or sum with set

petter — Wed, 24 Jan 2018 17:35:22 GMT

With the limited sample data this seems to work ... hacked together a bit quickly without much testing:

Re: Variable Pacing using rangesum or sum with set

Anonymous — Wed, 24 Jan 2018 18:36:05 GMT

Petter, I am not able to get into the file you have shared to see the formula.

Are you able to explain?

Re: Variable Pacing using rangesum or sum with set

petter — Wed, 24 Jan 2018 18:59:42 GMT

I have created a straight table:

Dimensions:

[Delivery Month]

Expressions:

Max([Date Year]) with label Date Year

Min([Date Month]) with label Date Month

Sum({<[Business Days Remaining in Month]=>} [Order Quantity]) with label Orders

Min([Business Days Remaining in Month]) with label Business Days left

You can drag and drop the resulting columns so you get them in the right position.

I have created a list box so you can select which "Business Days Remaining in Month" you want for the two months.