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: 
mruehl
Partner - Specialist
Partner - Specialist

Set Analysis within Set Analysis

I've got the following problem:

I need the price of a materail which is Status=1 and is the lowest price with the lowest priority.

I tried everything and searched through the community but I didn't find the right solution. Man Sad

Thanks in advance for your help!

For example:

2019-04-06 10_49_46-Qlik Sense Desktop.png

Input:
LOAD * INLINE [
Material, Priority, Price, Status
A, 1, 30, 2
A, 2, 20, 1
A, 2, 30, 1
A, 3, 10, 3
A, 1, 20, 1
B, 3, 40, 1
B, 2, 70, 5
B, 1, 80, 1
B, 1, 50, 1
];

Labels (1)
1 Solution

Accepted Solutions
Gysbert_Wassenaar

With your example data this should do the trick: FirstSortedValue({<Status={1}>}Price, Priority*1000000+Price)

talk is cheap, supply exceeds demand

View solution in original post

7 Replies
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Does it need to respond to selections when identifying the lowest priority?

If not, I would look at doing something in the load script like this:

MyData:
LOAD
  Material &'-'& num(Priority, '0000') as MaterialPriorityKey,
  Material,Priority,Status
etc...

LOAD
   1 as IsLowest,
   MinString(MaterialPriorityKey)
RESIDENT MyData
WHERE Status = 1
GROUP BY Material
;

That would then give you a flag you could then use in Set Analysis, like this: {<IsLowest={1}>}

If you wanted it to be dependent on selections, so if you selected Priorty 2 it would then give you that price as it would then be the lowest it gets a bit more messy. Let me know if you need to go there...

Steve

 

mruehl
Partner - Specialist
Partner - Specialist
Author

Thank you very much for your answer! In this case, it would be fine to do so in the script. But the main question for me and behind this post is: How can I do set analysis which includes dynamic selections per line? Another requirement is, to sum up the average turnover of the last 5 orders of a customer. Of course I could solve that within the script as well, but then it doesn't depend on the actual selection any more. Can you give ma hint?
Gysbert_Wassenaar

With your example data this should do the trick: FirstSortedValue({<Status={1}>}Price, Priority*1000000+Price)

talk is cheap, supply exceeds demand
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

As ever, @Gysbert_Wassenaar has given a great solution for this. Just to add that if you want to get a total of that expression you will need to use an AGGR. You are perhaps unlikely to want to get a total price, but if you want a total value it would look like this:

sum(aggr(FirstSortedValue({<Status={1}>}Price, Priority*1000000+Price) * Quantity,Material))

If you use this in a table which has Price, Priority and Status in the value will appear against an arbitrary row, rather than against the one which has the Status and the lowest Priority. If you are using this expression ensure you don't have those other fields as dimensions.

The section on AGGR on help.qlik.com should illuminate why the above is needed and works.

Hope that helps.

qliksus
Specialist II
Specialist II

Creating a field like

load * ,
Priority & Status as Key
from table ;

and use expression min(TOTAL<Material> {<Key={'11'}>}Price)
mruehl
Partner - Specialist
Partner - Specialist
Author

Thanks a lot for all the solutions!

The answer of@Gysbert_Wassenaar was the easiest one, but the other one without scripting provided by @stevedark worked as well!

I try to get on with it with my problem summing up the last 5 orders per customer.

 

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

The important difference between the two solutions is what happens when selections are made. Test to ensure you get the solution you want.