Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good morning guys
I have a questions regarding 'Set Analysis' of Qlik.
I have in my database:
1) a list of part prices - [Order Cost]
2) a list of part quantity - [Order Qty]
3) a list of dates for when the part was purchased - [Order Date]
4) 4 User defined Dates: vDateMin1, vDateMin2, vDateMax1, vDateMax2
I want to do price of part - based on the date range via Set Analysis:
Achieved via:
Sum({<[Order Date]={">=$(vDateMin1)<=$(vDateMax1)"}>}[Order Cost]*[Order Qty])
which returned very well the cost of the Part across a set date range.
I'm interested to know if Qlik has the function to set a range of date for [Order Cost] and a different range of date for [Order Qty], and multiply by each other,
Ie: set [Order Cost] with the condition of within vDateMin1, vDateMax1;
and [Order Qty] with a different condition of within vDateMin2, vDateMax2
You might be able to achieve this using the Aggr() function.
Sum(Aggr(Sum({<[Order Date]={">=$(vDateMin1)<=$(vDateMax1)"}>} [Order Cost]) * Sum({<[Order Date]={">=$(vDateMin2)<=$(vDateMax2)"}>} [Order Qty]), [Order ID]))
Here I am assuming that Order ID is the most granular level in your database. Replace it with what you have in your database.