Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

mruehl
Contributor III

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
MVP & Luminary
MVP & Luminary

Re: Set Analysis within Set Analysis

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

talk is cheap, supply exceeds demand
7 Replies
MVP & Luminary
MVP & Luminary

Re: Set Analysis within Set Analysis

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
Contributor III

Re: Set Analysis within Set Analysis

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?
MVP & Luminary
MVP & Luminary

Re: Set Analysis within Set Analysis

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

talk is cheap, supply exceeds demand
Highlighted
MVP & Luminary
MVP & Luminary

Re: Set Analysis within Set Analysis

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
Valued Contributor II

Re: Set Analysis within Set Analysis

Creating a field like

load * ,
Priority & Status as Key
from table ;

and use expression min(TOTAL<Material> {<Key={'11'}>}Price)
mruehl
Contributor III

Re: Set Analysis within Set Analysis

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.

 

MVP & Luminary
MVP & Luminary

Re: Set Analysis within Set Analysis

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