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

[israrkhan]

Martin,

i think concatenate is not a solution here , because in parts performance i have around 25 columns, and In target only 5 columns.

and the have same only Dealercode, nothing else...

but many thanks for your time...

Thanks and Regards

Israr

[israrkhan]

Error in calculated Dimension...

[Colin-Albert]

I would agree with Martin - Concatenate the target data into the parts performance data.

The Dealer, date & month fields etc. will have the same names as in the parts performance table, and the target value will be a new column. The charts then just sums the NetValue  and sums the Target data and becomes simple.

[israrkhan]

Zainab...

its working.. but again don't like Synthetic Keys .

Appreciated your idea....

[martinpohl]

so make a mapping table between PartPerformance and NST_Targets1 within 3 columns:

month

dealercode

month&dealercode as MonthDealer

create a field month&dealercode as MonthDealer in both tables.

the link to the dealer table should be already there so you only need a month table (or better a year/month table, so you should join year&month&dealercode.

regards

[israrkhan]

Albert thanks for your input,

But idea given by Zainab is working,

so shal i continue with this.. or concatenate will be better solution..?

[#8]

what about a calendar table?

[Anonymous]

I would also recommend Martin's solution. You need to add the time dimension to key.

BR

Serhan

[Colin-Albert]

I always concatenate target data to the  measures table as it makes the charts simpler with less likelihood for introducing loops and/or synthetic keys. One advantage of concatenating the target data is that you can easily report on dealers who have a target but no sales in a period .

That said, if your chart is now working, it is up to you to decide whether re-working the data model is worth it.

To avoid the synthetic key, create a new key field that concatenates the DealerCode & Month fields

[CELAMBARASAN]

In NST_Targets1

Remove the DealerCode Field

Instead form a composite key

DealerCode&NstMonth AS DealerCodeMonth

And in PartsPerformance table

Add a field as below

DealerCode&Month AS DealerCodeMonth