Data Model Question

[israrkhan]

Hi Community,

i have a calculating problem, have spent hours, but no luck..

Your Thoughts will be appreciated,

PartsPerformance: (Table)

As you can see in the PartsPerformance Table, i hvae NetValue,  can have Multiple values on daily basis.

and i Have A Month Field, in the format of Jan, Feb, Mar, Apr...and so...

NST_Targets1: (Table)

in NST_Targets1 Table, i have NST_Targets1.NSTMonth field in the format Jan, Feb , Mar, Apr.. so on..

and NST_Targets1.Targets field, which has Monthly Targets.one value for each month

OutPut: in Bar chart i added 1 Dimension Month, from PartsPerformance table

AND two expressions 1) Sum(NetValue), 2) sum(NST_Targets1.Targets)

Problem :

i can not see  NST_Targets1.Targets Correctly, it repeats same value for all months. even i have targets for All Months.

but sum(netValue) showing correct.

when i Removed Month from dimension and i added NST_Targets1.NSTMonth as dimension, now i can see Targets correctly, and sum(net value) goes wrong.

Stucked....Please share your thoughts...

Thanks And Regards

Khan

[#2]

maybe try renaming month columns so that they both have the same name

[israrkhan]

did not get you exactly,

did you mean, Rename the NST_Targets1.NSTMonth as Month..

so it will create a synthetic key?

Thanks

Israr

[#4]

yeah exactly

maybe someone else can help you fix this without having to create synthetic key but I would try this just to see if it works and if you get the desired data...

[israrkhan]

Zainab let me try...

but never like Synthetic Keys...

[martinpohl]

so rename and concatenate load the table NST_Targets1 with PartPerformance.

You will get one table within the fields month, dealer and all other field which are the same in both tables.

e.g.

concatenate (PartPerformance) load

NST_Targets1.NSTMonth as Month,

NST_Targets1.Category as Category,

NST_Targets1.NSTCountry as Country

and than you can sum(targets) and sum (net value) per month.

Regards

[#7]

I don't mind them, I try to avoid them but one or two don't have that much affect on my current data

[PrashantSangle]

Hi,

Try this as your dimension instead of month

=Only({<Month=p{ NST_Targets1.NSTMonth}>}Month)

[martinpohl]

the synthetic key has a problem.

if there are targets but no net value for one dealer / month you won't get the target values in your chart.

[#10]

oh yes of course, I have never had that issue as I have only created synthetic key where there needs to be value in both columns so I didn't think of that....