Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I've created a table with current available inventory and the sales of the period connected to selected dates.
Let's say I've selected a date period based on the field Date, for example MTD, (selecting all dates between July 1st to July 19th). what script should I write to count the number of days between my min date and max date in my selected range disregarding all other dimensions than "Date"?
Item No | Item Desc. | Inventory | Sales Qty of period | Avg. Daily Sales | Days of Supply |
---|---|---|---|---|---|
XXX123 | Banana | 100 | 53 | Sales Qty / Number of days | Inventory / Avg Daily Sales |
The the issue with using the above two proposals was that since not all items had transactions on all dates, the simple min max date script wouldn't work.
This was my solution in the end:
I solved it by creating a variable called "vNoSelectedDays"
defined in variable list as: =max(Date)-min(Date)+1
Average Daily Sales Calculation:
Sum([#Sales_QTY])/$(vNoSelectedDays)
Days of Supply Calculation:
Sum({1<vDisregardDate=>}[#Inventory_Remaining_QTY])
/
(Sum([#Sales_QTY])/$(vNoSelectedDays))
))
Hi,
if I understand your problem well it should be enough for you to use simple Count(distinct Date). This will count distinct number of dates in your date field. And if you select date range it will filter automaticaly.
Code:
Count({$<={">$(=Max(Date))<$(=Min(Date))"}>}[Number of days])
Don't forget !
When applicable please mark the appropriate replies as CORRECT https://community.qlik.com/docs/DOC-14806. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.
The the issue with using the above two proposals was that since not all items had transactions on all dates, the simple min max date script wouldn't work.
This was my solution in the end:
I solved it by creating a variable called "vNoSelectedDays"
defined in variable list as: =max(Date)-min(Date)+1
Average Daily Sales Calculation:
Sum([#Sales_QTY])/$(vNoSelectedDays)
Days of Supply Calculation:
Sum({1<vDisregardDate=>}[#Inventory_Remaining_QTY])
/
(Sum([#Sales_QTY])/$(vNoSelectedDays))
))