Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis function with different rules for different variables

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

1 Reply
sunny_talwar

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.