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: 
Anonymous
Not applicable

Urgent! Help needed in Set Analysis

Hi,

I am new with Set Analysis, and i am really stuck with something I am stuck big time and really need your help.

I am having one problem where I need your serious advise on,I have a Table

Sum( if(DWH_IM_BRANCH_ID<1000 , STOCK_QTY))

IN THE ABOVE LINE WE ARE TRYING TO ACHIEVE THE STOCK OF STORES.

BUT AS LONG AS THE CURRENT MONTH IS SELECTED IT SHOW THE VALUE SEE BELOW

q1.jpg

BUT IF I TAKE THE CURRENT MONTH OFF THE SELECTION IT DOES NOT GET ANY STOCK SEE THE IMAGE BELOW:

q2.jpg

AS YOU CAN SEE ABOVE ALL FIRST THREE COLUMNS ARE GONE ZERO, WHAT WE WANT THAT IRRELEVANT OF ANY SELECTION IT SHOW ALWAYS TAKE

THE CURRENT STOCK NO MATTER WHICH MONTH WE HAVE SELECTED.

Then I Used Set Analysis

sum ({$<DWH_IM_BRANCH_ID ={"<1000"}>}  STOCK_QTY)

then tried sum ({1<DWH_IM_BRANCH_ID ={"<20"}>}  STOCK_QTY)

It did the job but it was showing all 15000 Above items, i just want them to take Selection into considration but bot for the Stock_Qty.

Please Help and sorry about a long discussion Question.

13 Replies
Not applicable
Author

Hi

the 1 in the expression basically means you are ignoring all selections, have a look at the below thread for a steer on set analysis

Set Analysis: syntaxes, examples

hope that helps

Joe

joshabbott
Creator III
Creator III

In reply to:

It did the job but was showing all 15000 Above items, i just want them to take Selection into consideration but not for Stock_Qty

To use all selections, use '$' for your set identifier, not '1'

To remove the selections for 'Stock_Qty, add :

Stock_Qty=

Into your set modifier, these can be separated by comma's.

Anonymous
Not applicable
Author

Thanks Josh for you Quick Reply Can you please Tell me where should i Add and How Stock_Qty= Please in following Formula

sum ({$<DWH_IM_BRANCH_ID ={"<20"}>} STOCK_QTY)

joshabbott
Creator III
Creator III

sum({$<DWH_IM_BRANCH_ID={"<20"}, STOCK_QTY=>} STOCK_QTY)

This would take the set analysis expression you had, using any selections the user made, and remove any selections the user made to the 'STOCK_QTY' field.

Anonymous
Not applicable
Author

Thanks Josh,

I tried that it shows the result but if Supplier is selected its show the supplier first and the rest of other supplier are also under them i just want the selected suppliers. any thing you can suggest on that.

joshabbott
Creator III
Creator III


Based on what I'm guessing, Supplier is a dimension.  I'm guessing you also have multiple expressions.  I'm also guessing you have one expression with a :

sum({1}... somewhere in there, which would bring back all suppliers.

I'm just guessing here but not positive, let me know if you have any questions.

Anonymous
Not applicable
Author

You are absolutely right Josh, Supplier is a Dimension, and we have multiple expression but none of them have Sum({1}.. it was there before but when you asked to change i  changed it and I have just double check it again to make sure.. What should i do to avoid all the suppliers coming togeather regardless of selection.

joshabbott
Creator III
Creator III

Try disabling all the expressions except the one above, and then slowly start adding them back on until all the suppliers show up again.  That would be the expression to take a look at

Anonymous
Not applicable
Author

Yes Josh I did that and one by one everything goes fine as soon as I Disable this expression where I am using Set Analysis.