topic Re: Set analysis using a parameter - baffled ! in QlikView

Set analysis using a parameter - baffled !

Fri, 07 Oct 2011 10:37:08 GMT

HI ,

I have 2 sets of data I am trying to cross reference to get a sum..i.e.

Data Set 1

Branch Ref Range Name = 'A' branches and 'B' Branches etc.

Branch Number Range Defintion = (4,5,6) and (1,3,8) etc.

Data Set 2

Branch Number = 1,2,3,4,5 etc.

Min Stock Level (multiple item values per branch) =10, 25, 15, 4, 2 etc.

I am trying to do a chart which has the Branch Ref Range Name & then the sum of the min stock levels based on this range i.e.

Branch Ref Range Name Sum Of Min Stock Levels

'A' Branches 76

'B' Branches 42

etc.

I have tried Branch Ref Range Name as a dimension & then

sum( {$<BRANCH Number={Branch Number Range Defintion}>} MIN_LEVEL ) as an expression but this just does not work

Does anyone have any ideas please (I attached an example file)

Any help greatly appreciated

Thanks

Nick

Re: Set analysis using a parameter - baffled !

kaushiknsolanki — Fri, 07 Oct 2011 11:13:01 GMT

Hi,

Have a look at the updated application i have attached.

Regards,

Kaushik Solanki

Re: Set analysis using a parameter - baffled !

Fri, 07 Oct 2011 11:33:02 GMT

Thanks for the reply but this hasn't quite acheieved what I was after. I have fixed the bad data and created just one central object that hopefully illustrates better what I cannto get to work in the 1st column. The 2nd column inlcudes your original formaule which unfortunately does not work either in this scenario -(

Any more help appreciated

Thanks

Nick

Re: Set analysis using a parameter - baffled !

Fri, 07 Oct 2011 11:37:13 GMT

How do i add a new attachment please - i cannot find a link any more ?

Thanks

Nick

Re: Set analysis using a parameter - baffled !

kaushiknsolanki — Fri, 07 Oct 2011 11:37:54 GMT

Hi,

Then can you please explain me in detail, with example, what output you want.

Regards,

Kaushik Solanki

Re: Set analysis using a parameter - baffled !

Fri, 07 Oct 2011 11:42:02 GMT

Click on Use advanced editior to attach when replying

Re: Set analysis using a parameter - baffled !

Fri, 07 Oct 2011 11:42:17 GMT

This is the expression I am trying to get to work..

sum( {$<BRANCH={BRANCH_RANGE_VALUES}>} MIN_LEVEL )

with BRANCH_RANGE_NAME as a dimonsion in a chart (pivot table)

Thankyou 🙂

Re: Set analysis using a parameter - baffled !

Fri, 07 Oct 2011 11:44:39 GMT

Attachment thankyou 🙂

Re: Set analysis using a parameter - baffled !

kaushiknsolanki — Fri, 07 Oct 2011 11:57:32 GMT

Hi,

It will not work cause there is no connection in Branch range and the branch as well as min level.

In order to get the min level there should be a dimension which is linked to it, and in your case only branch is lined to min level, so only branch can be the dimension in your case.

You can create a calculated dimension. say

If(match(branch(20,21,22,23,24,25,26,27,28,29),'Branch 20 all',.......

If you can provide me the xls file then i will show you how you can do this.

Regards,

Kaushik Solanki

Re: Set analysis using a parameter - baffled !

Fri, 07 Oct 2011 12:02:03 GMT

HI there,

Thanks for the reply, but based on what you are saying we woudl have to accomodate every possible combination in the calculated dimensions and although the example only has a few line, the real data has hundreds of lines, so I am thiniing I will have to manipulate the data a different way before importing -(

Thankyou anyway !

Nick

Re: Set analysis using a parameter - baffled !

kaushiknsolanki — Fri, 07 Oct 2011 12:05:49 GMT

Hi,

Then i feel some how you need to link those two tables and then you can use them for calculations.

Regards,

Kaushik Solanki