Days of supply based on sales from selected date period? (count days between min & max selected dates)

[rickardwestling]

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

[DavidŠtorek]

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.

[ramkrishna86]

Code:

Count({$<={">$(=Max(Date))<$(=Min(Date))"}>}[Number of days])

[pablolabbe]

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.

[rickardwestling · Accepted solution]

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))

))