Fields giving 0 result

[#1]

Hi guys,

I run an SQl query to get the required fields.

now when I add all the fields and designs that I need of them is making a mess.

it is called division.

On my fields I have Division, Department, Names and revenue.

everything runing perfect with out having the division field selected.

once I select one of the divison options the revenue shows as 0.

also, when selected it is recognizing the right departments but keeping them white and turning the other departments which are not related to that division in gray color.

Any Idea on why the revenue turning into Zeros.

Thanks

[#2]

I think I am very close but not sure about the solution.

As I said before the Department lebels are coming from an external spreadsheet where A is the BSUNIT and B is the department.

Could I add Division lebel under C and then map it?

[swuehl]

If I see correctly, you are mapping Manager2 in table BackLogSplit1 both to Division and Department, is this correct?

In PNL table, you use a different mapping for Department BusinessUnit_Map. If I see correctly, this Department will link your two tables. Are the Department values compatible in both tables, i.e. do you get any matching values if you map from the two different mapping tables?

You can't just add another column C to your mapping load, resulting in mapping two columns in, but you can e.g. join another table or use another mapping with the same lookup key.

[#4]

That's true.

I am using the Department_Map on both sides.

PNL:

LOAD *,

    applymap('BusinessUnit_Map', BUSUNIT, 'Other') as Department;   

Backlog:

LOAD *,

    applymap('Department_Map', MANAGER2, 'Other') as Department;

But I am getting Zero on Billed revenue if I add the division field.

When I choose any of the divisions, it recognize the right departments but it shows zero billed revenue wich is coming from the Amount field on the PNl query.

I tried to add the column to the spreadsheet and I was able to get the right amount for the billed revenue but I got zeros on the backlog..

so still have an error somewhere

[swuehl]

Sorry, I am getting confused.

badrharfoush wrote:

That's true.

I am using the Department_Map on both sides.

PNL:

LOAD *,

    applymap('BusinessUnit_Map', BUSUNIT, 'Other') as Department;   

Backlog:

LOAD *,

    applymap('Department_Map', MANAGER2, 'Other') as Department;

You are using two different mapping tables here, aren't you, not Department_Map on both sides?

[#6]

Yep two mapping tables

[#7]

this image below shows when I choose one of the divisions

[#8]

and this image shows the results if I highlight the departments without dealing with the divisions.

[swuehl]

Strange.

What expressions do you use?

[#10]

to calculate the billed revenue I use a varible with this expression:

=sum(

    if(

        ACCTCAT < valuelist(600) AND LEDGERTYPE='AA',

            if(InMonthToDate(ORDERDATE, Today(), 0), AMOUNT* -1)

    )

)

[swuehl]

Have you checked how your selection in Division resp. Department affects the possible field values for ACCTCAT, LEDGERTYPE, ORDERDATE and AMOUNT?

I suspect that your explicit selection in department is limiting your data in a way that one or more of these fields won't have possible values anymore.

But I still don't really understand what is going on. I think it would make things much easier if you could find a way of uploading a sample app.