Set analysis function with different rules for different variables

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set analysis function with different rules for different variables

sunny_talwar — Thu, 26 May 2016 10:52:25 GMT

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.

topic Re: Set analysis function with different rules for different variables in QlikView

Set analysis function with different rules for different variables

Re: Set analysis function with different rules for different variables