topic Re: Urgent! Help needed in Set Analysis in QlikView

Urgent! Help needed in Set Analysis

Anonymous — Wed, 30 Jul 2014 15:41:06 GMT

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

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

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.

Re: Urgent! Help needed in Set Analysis

Wed, 30 Jul 2014 15:50:53 GMT

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

Re: Urgent! Help needed in Set Analysis

joshabbott — Wed, 30 Jul 2014 15:56:24 GMT

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.

Re: Urgent! Help needed in Set Analysis

Anonymous — Wed, 30 Jul 2014 16:37:18 GMT

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)

Re: Urgent! Help needed in Set Analysis

joshabbott — Wed, 30 Jul 2014 16:41:23 GMT

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.

Re: Urgent! Help needed in Set Analysis

Anonymous — Wed, 30 Jul 2014 16:52:40 GMT

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.

Re: Urgent! Help needed in Set Analysis

joshabbott — Wed, 30 Jul 2014 17:00:28 GMT

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.

Re: Urgent! Help needed in Set Analysis

Anonymous — Wed, 30 Jul 2014 17:21:23 GMT

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.

Re: Urgent! Help needed in Set Analysis

joshabbott — Wed, 30 Jul 2014 17:23:01 GMT

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

Re: Urgent! Help needed in Set Analysis

Anonymous — Wed, 30 Jul 2014 17:39:49 GMT

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.

Re: Urgent! Help needed in Set Analysis

joshabbott — Wed, 30 Jul 2014 17:49:56 GMT

So if you have just a straight table with 'Supplier' as the dimension, and 'sum({$<DWH_IM_BRANCH_ID={"<20"}, STOCK_QTY=>} STOCK_QTY)' as the expression, and you select only 1 supplier, all the suppliers still show up?

Re: Urgent! Help needed in Set Analysis

Anonymous — Wed, 30 Jul 2014 17:55:17 GMT

YES

Re: Urgent! Help needed in Set Analysis

joshabbott — Wed, 30 Jul 2014 17:56:20 GMT

Any chance you could attach your document or a part of it?

Re: Urgent! Help needed in Set Analysis

Anonymous — Wed, 30 Jul 2014 18:03:48 GMT

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

I tried this and Looks like its fine. Thank you so much for your Help really Appreciate that you are so helping.