Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Data based on different date variables?

Hi All,

I have a simple question, but probably with a complicated answer.

I have a data table which contains simplified:

Customer - Order - Article - #Delivered - #Sales - #Returns - OrderDATE - SalesDATE

Now I get stock reports from the customers which contain the following:

Customer - Article - #Stockamount - StockDATE


Now I want to calculate their #Stockamount and our virtual stock amount (as a sum: #Delivered - #Sales - #Returns = stock) and compare those.


But now I only want to use their latest StockDATE (ie. sum({<StockDATE = {$(=max(StockDATE))}>} #Stockamount)) to calculate their stock.


Here I run into problem 1; It will only calculate based on the max StockDATE found, not per customer in the table., untill I select a customer.


The second problem I have is that I want to base the #Delivered, #Sales and #Returns on DATE, everything between first known date UNTILL the max StockDATE, And this is different for each customer as well.


Any thought or ideas?

2 Replies
marcus_sommer

Try for your first topic something like this:

sum(aggr(firstsortedvalue(#Stockamount, -StockDATE), Customer))


By your second topic I'm not sure if something similar could be applied. Quite often it's helpful to precalculate such things like the max(StockDATE) within the script like:


MaxStockDATE:

Load Customer, max(StockDATE) as MaxStockDATE Resident abc Group by Customer;


and use this field within your expressions.


- Marcus

Not applicable
Author

Thanks for your reply. The firstsortedvalue aggr doesnt give me any good results.

About the second part, I have added that to the script already. But How can I filter? It won't be linked to any of my dates whatsoever and I can't get it to work properly in my expressions. I will post an example asap. Reloading as we speak..