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: 
dirk_fischer
Creator
Creator

Set Analysis problem

Hi everybody,

I'm struggling with a set analysis challenge again and hope, somebody can help me out. I have defined a table, where I have measurements for a product and the corresponding upper and lower limits, where the measurements have to be for the product to be a good product.

In parallel, I have counted how often a specific value for the measurement occurs. Now I try find out how many products were in tolerance (good quality) and how many products were out of tolerance (bad Quality).

I thought that

Sum({$<PROPERTY_VALUE={">=$(=Max(ADAPTATION_DEFAULT_LL)) <=$(=Max(ADAPTATION_DEFAULT_UL))"}>} QUANTITY

   )

or

Sum({$<PROPERTY_VALUE={">=$(=Only(ADAPTATION_DEFAULT_LL)) <=$(=Only(ADAPTATION_DEFAULT_UL))"}>} QUANTITY

   )

should do the trick, but unfortunately it doesn't.

If I use Only(ADAPTATION_DEFAULT_LL) in the same table, it shows the proper values for the lower limit of the corresponding measurement, but using it in the set Analysis does not come up with the desired result.

If I use something hardcoded like

Sum({$<PROPERTY_VALUE={">=0 <=20"}>} QUANTITY  )

everything is fine.

Does anybody have an idea, what's wrong with my statement?

Already in advance, I want to thank you for your help.

Best regards,

Dirk

             .

1 Solution

Accepted Solutions
sunny_talwar

So it seems that you have limits which are dependent on your dimension. In this case, set analysis will not work because set analysis is evaluated once per chart. You will need to use Aggr() function with if statement to get this accomplished

Sum(Aggr(If(PROPERTY_VALUE = >= ADAPTATION_DEFAULT_LL and PROPERTY_VALUE <= ADAPTATION_DEFAULT_UL, QUANTITY), Dimensions))

I am not sure what Dimensions you need here, but the general idea is to use Aggr() with if statement above

View solution in original post

6 Replies
rupamjyotidas
Specialist
Specialist

Shouldn't it be

Sum({$<PROPERTY_VALUE={">=$(=Min(ADAPTATION_DEFAULT_LL)) <=$(=Max(ADAPTATION_DEFAULT_UL))"}>} QUANTITY

   )

sunny_talwar

Does this in a text box object give you 0

=Max(ADAPTATION_DEFAULT_LL)

and this gives you 20?

=Max(ADAPTATION_DEFAULT_UL)

dirk_fischer
Creator
Creator
Author

Hi Sunny,

I get 0 for =Max(ADAPTATION_DEFAULT_LL) if I use it in the same table as additional key figuer and 20 for =Max(ADAPTATION_DEFAULT_UL) if I use it in the same table.

In a textbox, I get 50 as expected, because I have more than one measurement, if I don't select a specific measurement. But you're right, it's working correctly, if I select a measurement.

However, as long as nothing is selected, I want to display the results for all measurements with their default tolerances and then in a second step build up a selection panel for upper and lower tolerance, valid only for one measurement to allow playing around and doing some simulations.

I have only one "possible" combination of upper and lower Limit per measurement. Is there a way to use the P() function for this?

Regards,

Dirk

sunny_talwar

So it seems that you have limits which are dependent on your dimension. In this case, set analysis will not work because set analysis is evaluated once per chart. You will need to use Aggr() function with if statement to get this accomplished

Sum(Aggr(If(PROPERTY_VALUE = >= ADAPTATION_DEFAULT_LL and PROPERTY_VALUE <= ADAPTATION_DEFAULT_UL, QUANTITY), Dimensions))

I am not sure what Dimensions you need here, but the general idea is to use Aggr() with if statement above

dirk_fischer
Creator
Creator
Author

Yes, that's the general setup. The (sensor) measurement is the dimension and has an upper and lower tolerance value.

I need to figure out, what dimensions I have to use. But that's something for tomorrow.

I will let you know, if I found the solution.

In the meantime, thank's a lot for your help.

Regards

Dirk

dirk_fischer
Creator
Creator
Author

Hi Sunny

I managed to get it right. I had to use the sum on the quantity combined with PROPERTY and PROPERTY_VALUE to get the correct results.

So in the end the Statement Looks like:

Sum(Aggr(If( (PROPERTY_VALUE >= ADAPTATION_DEFAULT_LL) And

    (PROPERTY_VALUE <= ADAPTATION_DEFAULT_UL), Sum(QUANTITY) ), PROPERTY, PROPERTY_VALUE))

Thank's a lot for your help.

Regards

Dirk