Set analysis using a parameter - baffled !

[#1]

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

[kaushiknsolanki]

Hi,

     Have a look at the updated application i have attached.

Regards,

Kaushik Solanki

[#3]

Hi

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

[#4]

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

Thanks

Nick

[kaushiknsolanki]

Hi,

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

Regards,

Kaushik Solanki

[#6]

Click on Use advanced editior to attach when replying

[#7]

HI

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 🙂

[#8]

Attachment thankyou 🙂

[kaushiknsolanki]

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   

[#10]

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