time of product in company

[#1]

Hi community,

i run around in my data and had another idea for something i can display.

What about a KPI showing the timerange in days an article was in store from delivery to the store till it was

sold.

So we have articlenumber, amount,sold/delivery, date.

The thing is that there can be 2 deliveries of a product before even one sales.

But with diffdate i only get the datedifferenc to the last deliverie of the same product.

So maybe create a var with the amount coming in from delivery and use this as the delivery date for the diffdate expression as long its not 0.And when its 0 switch to the next delivery date, so we allways have the right date.

And an if,if  there is no delivery date before the sold-date i want to take the start date of the timerange.

The problem here, again i have a great idea but no clue about how i can achiev this in the right way.

So maybe u can give me some tipps.

Thanks for help in advance

-Eric

[Gysbert_Wassenaar]

Sounds like something like Alt([Sold Date],[Start Date of Timerange]) - Min({<[Delivery Date]={'>0'}>}[Delivery Date])

[#3]

Hi Gysbert,

thanks for ur answer. make sense i just need to test if its possible wit my field with sold and delivery and the start date because I let the user pick daterange via daterange picker.

So I think firstly i will let the start date of Timerange out of my calculation,and when there is no delivery date i dont take it into consideraion.

thx

[#4]

Hi Gysbert,

once again i tried a few things but dont get it to work .

where a bit of code of my loadscript to calc the diffdate:

temp:

NOCONCATENATE

load articlenumber,Date_VD as delivery_date

Resident InitialLoad

where "sold/delivery" ='delivery';

left join

load articlenumber,Date_VD as sold_date

Resident InitialLoad

where "sold/delivery" ='sold';

final:

Load articlenumber,delivery_date,sold_date,

sold_date - delivery_date as diffdate resident temp;

[Gysbert_Wassenaar]

If you have multiple delivery dates and you only want to use the earliest one then try this:

temp:

NOCONCATENATE

load articlenumber, min(Date_VD) as delivery_date

Resident InitialLoad

where "sold/delivery" ='delivery'

group by articlenumber;

[#6]

thx for that tipp and there is the point where my trouble begins with the delivery date.

Because of mutliple delivery dates i must implement some sort of var that count down the amount from delivery down and then when the amount hits zero switch do the second earliest.

hope i explain it decent enough.

[Gysbert_Wassenaar]

Perhaps you can find a solution in similar discussions about inventory:

https://community.qlik.com/search.jspa?q=inventory+days

[#8]

thx for this link,

searched around a bit, and now i think i do it bit different.

i think its better to find out the delivery date first and add it as a column so i dont have the problem with the search for delivery date and calc at the same time.

Hope u can help me with the find earliest delivery date, and add a counter(var) and switch to the next on if zero.

thanks in advance

-Eric