Date range based on other field

jorgen_santberg — Sat, 16 Nov 2024 17:55:55 GMT

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.

Re: Date range based on other field

edwin — Thu, 22 Oct 2020 18:10:59 GMT

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;

topic Re: Date range based on other field in QlikView

Date range based on other field

Re: Date range based on other field