topic Re: Building a formula to get the right date in QlikView

Building a formula to get the right date

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I need your help in the following issue.

I have loaded to QV the set of following data regarding stock movements:

Product Code – Code of stock item

Incoming_Doc – if value > 0, stock document used to enter new products into stock, if value < 0, stock document decreased with quantities and values leaving stock (for production)

Outgoing_Doc – doc number used for outgoing products

Date – dates of stock movements

Quantity – kilograms/ metres etc of materials

Value – value of materials

Now, I would like to know for each Product_Code the following:

Oldest incoming date– the oldest date of still opened Incoming_Doc for given Product Code.

Incoming_Doc is still opened if its quantity (taking all outgoing moves) is still >0

Closed incoming_Doc --> the quantity = 0

PZ/OL 03950/2008 is a closed Incoming_Doc:

Newest outgoing date – the newest date for the outgoing stock movement for given Product_Code

Quantity and Value should show the totals for given Product_Code.

so the table I am trying to build should look like that:

I have no idea how to get the red dimensions. Could you please help me?

Re: Building a formula to get the right date

Anil_Babu_Samineni — Fri, 28 Apr 2017 19:45:42 GMT

You have same in qlikview file. What was the issue?

Re: Building a formula to get the right date

Gysbert_Wassenaar — Sun, 30 Apr 2017 13:22:56 GMT

Try adding these two expressions:

incomming: FirstSortedValue({<Incomming_Doc={"=sum(QUANTITY)>0"}>}Incomming_Doc,Date)

outgoing: FirstSortedValue(Outgoing_Doc,-Date)

Re: Building a formula to get the right date

Sun, 30 Apr 2017 17:34:44 GMT

‌thanks Gysbert,

I starter to doubt I get any answer. Now I am on holiday but when I come back in the middle of next weekend,I try and let you know.

BRgds,

Robert

Re: Building a formula to get the right date

Wed, 03 May 2017 10:14:21 GMT

Dear Gysbert,

The function doest not work in a way I want ...yet.

Starting from newest outgoing date - your formula: FirstSortedValue(Outgoing_Doc,-Date), however:

- I would like to get the "Date" as the outcome, and not the "Outgoing_Doc" (so, just replace Outgoing_Doc with Date ... BUT ...

- I would like to get this newest date for which the Outgoing_Doc exists for a given Product_Code.

If I would leave the formula with the following syntax: =FirstSortedValue(distinct Date,-Date), then if yesterday we entered into stock product "123", and a week ago it was the last time product "123" left stock, I get yesterday, while I want to get a week ago date.

Let's have this one sorted first before sorting out the Oldest Incomming.

Thanks a lot for your help in advance

Re: Building a formula to get the right date

Gysbert_Wassenaar — Wed, 03 May 2017 17:36:04 GMT

Starting from newest outgoing date - your formula: FirstSortedValue(Outgoing_Doc,-Date), however:

- I would like to get the "Date" as the outcome, and not the "Outgoing_Doc" (so, just replace Outgoing_Doc with Date ... BUT ...

- I would like to get this newest date for which the Outgoing_Doc exists for a given Product_Code.

What does "the Outgoing_Doc" mean? Is that a specific value or just any value as long as it's not null? If the latter then try Max({<Incomming_Doc={"=sum(QUANTITY)>0"}, Outgoing_Doc={'*'}>}Date).

Re: Building a formula to get the right date

Thu, 04 May 2017 14:39:39 GMT

hi,

Outgoing_Document is a string. each stock out movement must have its unique outgoing doc number.

I experimented with FirstSortedValue, and the below formula gives me perfectly the dates I wanted:

FirstSortedValue( {<Outgoing_doc= {"*"} >} distinct Date,-Date)

thanks a lot Gysbert for your hint with FirstSortedValue - I did not know this function, and after some research, it does the job.

Brgds

Robert