Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
With the limited sample data this seems to work ... hacked together a bit quickly without much testing:
Petter, I am not able to get into the file you have shared to see the formula.
Are you able to explain?
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.