Date range based on other field

jorgen_santberg · ‎2020-10-22

Hi all,

I am trying to get the sum of sales in a certain date range based on the leadtime per product.

So I want to know how many sales there will be between the start of next year and the start of next year + the leadtime. The leadtime may differ per product.

The dimensions I use in my table are product and product_desc. The (predicted) sales are per day and so is the leadtime (for example: leadtime = 28 means leadtime = 28 days).

The formula I use is:

=(Sum({<MonthYear=, Year=,Time={">$(=Date(YearEnd(Max(Time)))) <=$(=Date(YearEnd(Max(Time))+Leadtime))"} >}Sales))

I get the correct quantity if only one product is selected, but without this selection it gives me all the predicted sales.

I hope you guys can help me out.

edwin · ‎2020-10-22

the reason you get a value when one product is selected is bec only one leadtime is possible and the expression can be computed properly. when no product is selected there will be multiple leadtimes and the calculation cant be done properly.

assuming you allow the user to select a year so that the dashboard knows which projection year it will calculate, you can associate the year to possible dates per product. if no year is selectable then the other assumption is that you only have sales for the next year which may not make sense.

if you then associate the year to the valid future dates per product, your chart shouold just inlude Prod, Prod Desc, and expression is =sum(Sales). also add a list for years and make it always one selected so the user always has to select one year. how to test: in your chart, temporarily add Date (Sales Date) so you see which dates per product is being calculated.

this is how you can associate year:

Products:
load * inline [
Prod, ProdDesc, Leadtime
P1, Prod1, 20
P2, Prod2, 18
P3, Prod3, 33
];

Projections:
load *, Prod & '|' & date(Date,'M/D/YYYY') as %BridgeKey inline [
Date, Prod, Sales
1/1/2021,P1,669
1/2/2021,P1,943
1/3/2021,P1,441
1/4/2021,P1,232
1/5/2021,P1,422
1/6/2021,P1,533
1/7/2021,P1,730
1/8/2021,P1,944
1/9/2021,P1,823
1/10/2021,P1,621
1/11/2021,P1,847
1/12/2021,P1,269
1/13/2021,P1,949
1/14/2021,P1,955
1/15/2021,P1,441
1/16/2021,P1,945
1/17/2021,P1,907
1/18/2021,P1,772
1/19/2021,P1,700
1/20/2021,P1,337
1/21/2021,P1,280
1/22/2021,P1,731
1/23/2021,P1,143
1/24/2021,P1,947
1/25/2021,P1,444
1/26/2021,P1,963
1/27/2021,P1,97
1/28/2021,P1,564
1/29/2021,P1,371
1/30/2021,P1,471
1/31/2021,P1,842
2/1/2021,P1,372
2/2/2021,P1,594
2/3/2021,P1,771
2/4/2021,P1,67
2/5/2021,P1,312
2/6/2021,P1,263
2/7/2021,P1,294
2/8/2021,P1,431
2/9/2021,P1,769
2/10/2021,P1,241
2/11/2021,P1,720
2/12/2021,P1,18
2/13/2021,P1,869
2/14/2021,P1,205
2/15/2021,P1,128
2/16/2021,P1,799
2/17/2021,P1,474
2/18/2021,P1,908
2/19/2021,P1,89
2/20/2021,P1,955
2/21/2021,P1,924
2/22/2021,P1,421
2/23/2021,P1,118
2/24/2021,P1,326
2/25/2021,P1,349
2/26/2021,P1,368
2/27/2021,P1,247
2/28/2021,P1,887
3/1/2021,P1,60
3/2/2021,P1,291
1/1/2021,P2,850
1/2/2021,P2,248
1/3/2021,P2,379
1/4/2021,P2,776
1/5/2021,P2,773
1/6/2021,P2,552
1/7/2021,P2,592
1/8/2021,P2,284
1/9/2021,P2,755
1/10/2021,P2,47
1/11/2021,P2,803
1/12/2021,P2,209
1/13/2021,P2,287
1/14/2021,P2,803
1/15/2021,P2,237
1/16/2021,P2,40
1/17/2021,P2,575
1/18/2021,P2,627
1/19/2021,P2,876
1/20/2021,P2,337
1/21/2021,P2,830
1/22/2021,P2,386
1/23/2021,P2,1
1/24/2021,P2,560
1/25/2021,P2,914
1/26/2021,P2,690
1/27/2021,P2,415
1/28/2021,P2,646
1/29/2021,P2,106
1/30/2021,P2,80
1/31/2021,P2,751
2/1/2021,P2,884
2/2/2021,P2,939
2/3/2021,P2,248
2/4/2021,P2,312
2/5/2021,P2,602
2/6/2021,P2,999
2/7/2021,P2,213
2/8/2021,P2,298
2/9/2021,P2,596
2/10/2021,P2,108
2/11/2021,P2,361
2/12/2021,P2,313
2/13/2021,P2,90
2/14/2021,P2,949
2/15/2021,P2,70
2/16/2021,P2,263
2/17/2021,P2,718
2/18/2021,P2,767
2/19/2021,P2,365
2/20/2021,P2,651
2/21/2021,P2,654
2/22/2021,P2,4
2/23/2021,P2,557
2/24/2021,P2,455
2/25/2021,P2,572
2/26/2021,P2,635
2/27/2021,P2,625
2/28/2021,P2,249
3/1/2021,P2,141
3/2/2021,P2,92
1/1/2021,P3,895
1/2/2021,P3,670
1/3/2021,P3,816
1/4/2021,P3,394
1/5/2021,P3,989
1/6/2021,P3,501
1/7/2021,P3,11
1/8/2021,P3,833
1/9/2021,P3,195
1/10/2021,P3,34
1/11/2021,P3,224
1/12/2021,P3,734
1/13/2021,P3,534
1/14/2021,P3,243
1/15/2021,P3,788
1/16/2021,P3,440
1/17/2021,P3,224
1/18/2021,P3,862
1/19/2021,P3,217
1/20/2021,P3,370
1/21/2021,P3,446
1/22/2021,P3,294
1/23/2021,P3,449
1/24/2021,P3,202
1/25/2021,P3,357
1/26/2021,P3,201
1/27/2021,P3,700
1/28/2021,P3,48
1/29/2021,P3,932
1/30/2021,P3,518
1/31/2021,P3,531
2/1/2021,P3,211
2/2/2021,P3,60
2/3/2021,P3,635
2/4/2021,P3,458
2/5/2021,P3,40
2/6/2021,P3,255
2/7/2021,P3,252
2/8/2021,P3,86
2/9/2021,P3,522
2/10/2021,P3,575
2/11/2021,P3,203
2/12/2021,P3,240
2/13/2021,P3,192
2/14/2021,P3,626
2/15/2021,P3,856
2/16/2021,P3,564
2/17/2021,P3,299
2/18/2021,P3,622
2/19/2021,P3,893
2/20/2021,P3,324
2/21/2021,P3,0
2/22/2021,P3,848
2/23/2021,P3,74
2/24/2021,P3,980
2/25/2021,P3,64
2/26/2021,P3,354
2/27/2021,P3,862
2/28/2021,P3,910
3/1/2021,P3,661
3/2/2021,P3,592
1/1/2022,P1,897
1/2/2022,P1,205
1/3/2022,P1,760
1/4/2022,P1,119
1/5/2022,P1,204
1/6/2022,P1,989
1/7/2022,P1,503
1/8/2022,P1,188
1/9/2022,P1,250
1/10/2022,P1,533
1/11/2022,P1,980
1/12/2022,P1,658
1/13/2022,P1,265
1/14/2022,P1,382
1/15/2022,P1,125
1/16/2022,P1,582
1/17/2022,P1,364
1/18/2022,P1,7
1/19/2022,P1,345
1/20/2022,P1,48
1/21/2022,P1,196
1/22/2022,P1,412
1/23/2022,P1,853
1/24/2022,P1,410
1/25/2022,P1,297
1/26/2022,P1,728
1/27/2022,P1,87
1/28/2022,P1,711
1/29/2022,P1,364
1/30/2022,P1,62
1/31/2022,P1,916
2/1/2022,P1,433
2/2/2022,P1,106
2/3/2022,P1,509
2/4/2022,P1,659
2/5/2022,P1,146
2/6/2022,P1,902
2/7/2022,P1,788
2/8/2022,P1,762
2/9/2022,P1,789
2/10/2022,P1,905
2/11/2022,P1,561
2/12/2022,P1,917
2/13/2022,P1,960
2/14/2022,P1,322
2/15/2022,P1,227
2/16/2022,P1,799
2/17/2022,P1,196
2/18/2022,P1,444
2/19/2022,P1,244
2/20/2022,P1,945
2/21/2022,P1,965
2/22/2022,P1,0
2/23/2022,P1,500
2/24/2022,P1,713
2/25/2022,P1,517
2/26/2022,P1,24
2/27/2022,P1,815
2/28/2022,P1,616
3/1/2022,P1,878
3/2/2022,P1,5
1/1/2022,P2,230
1/2/2022,P2,515
1/3/2022,P2,175
1/4/2022,P2,564
1/5/2022,P2,388
1/6/2022,P2,205
1/7/2022,P2,533
1/8/2022,P2,946
1/9/2022,P2,194
1/10/2022,P2,355
1/11/2022,P2,615
1/12/2022,P2,50
1/13/2022,P2,467
1/14/2022,P2,900
1/15/2022,P2,370
1/16/2022,P2,56
1/17/2022,P2,791
1/18/2022,P2,482
1/19/2022,P2,25
1/20/2022,P2,367
1/21/2022,P2,884
1/22/2022,P2,479
1/23/2022,P2,176
1/24/2022,P2,512
1/25/2022,P2,860
1/26/2022,P2,940
1/27/2022,P2,328
1/28/2022,P2,424
1/29/2022,P2,194
1/30/2022,P2,91
1/31/2022,P2,295
2/1/2022,P2,409
2/2/2022,P2,804
2/3/2022,P2,956
2/4/2022,P2,887
2/5/2022,P2,219
2/6/2022,P2,579
2/7/2022,P2,433
2/8/2022,P2,609
2/9/2022,P2,614
2/10/2022,P2,201
2/11/2022,P2,311
2/12/2022,P2,404
2/13/2022,P2,361
2/14/2022,P2,44
2/15/2022,P2,848
2/16/2022,P2,517
2/17/2022,P2,291
2/18/2022,P2,305
2/19/2022,P2,680
2/20/2022,P2,891
2/21/2022,P2,38
2/22/2022,P2,821
2/23/2022,P2,898
2/24/2022,P2,764
2/25/2022,P2,595
2/26/2022,P2,813
2/27/2022,P2,673
2/28/2022,P2,467
3/1/2022,P2,847
3/2/2022,P2,427
1/1/2022,P3,745
1/2/2022,P3,535
1/3/2022,P3,86
1/4/2022,P3,530
1/5/2022,P3,330
1/6/2022,P3,665
1/7/2022,P3,210
1/8/2022,P3,171
1/9/2022,P3,763
1/10/2022,P3,787
1/11/2022,P3,533
1/12/2022,P3,28
1/13/2022,P3,588
1/14/2022,P3,360
1/15/2022,P3,513
1/16/2022,P3,657
1/17/2022,P3,371
1/18/2022,P3,895
1/19/2022,P3,226
1/20/2022,P3,965
1/21/2022,P3,216
1/22/2022,P3,500
1/23/2022,P3,468
1/24/2022,P3,740
1/25/2022,P3,252
1/26/2022,P3,288
1/27/2022,P3,796
1/28/2022,P3,747
1/29/2022,P3,338
1/30/2022,P3,586
1/31/2022,P3,736
2/1/2022,P3,524
2/2/2022,P3,998
2/3/2022,P3,113
2/4/2022,P3,312
2/5/2022,P3,440
2/6/2022,P3,633
2/7/2022,P3,90
2/8/2022,P3,907
2/9/2022,P3,505
2/10/2022,P3,419
2/11/2022,P3,705
2/12/2022,P3,931
2/13/2022,P3,278
2/14/2022,P3,851
2/15/2022,P3,430
2/16/2022,P3,68
2/17/2022,P3,977
2/18/2022,P3,323
2/19/2022,P3,571
2/20/2022,P3,225
2/21/2022,P3,836
2/22/2022,P3,885
2/23/2022,P3,340
2/24/2022,P3,890
2/25/2022,P3,335
2/26/2022,P3,783
2/27/2022,P3,674
2/28/2022,P3,482
3/1/2022,P3,572
3/2/2022,P3,249
];

tmpBridge:
load date(date('1/1/2020')+RowNo() -1) as KeyDate
AutoGenerate(1096);

inner join (tmpBridge)
load KeyDate as Date
Resident
tmpBridge;


inner join (tmpBridge)
load Prod, Leadtime
Resident
Products;


NoConcatenate
Bridge:
load
	yearstart(KeyDate) as KeyDate,
	Prod & '|' & Date(Date,'M/D/YYYY') as %BridgeKey,
	Leadtime as Bridgeleadtime
Resident
	tmpBridge
where 
	Date>=AddYears(YearStart(KeyDate),1)
	and Date(Date<=AddYears(YearStart(KeyDate),1)+Leadtime)
;

drop table tmpBridge;

General Question