Pivot Table Repeating Value

[israrkhan]

Hi Community,

in my pivot table ,

i have 3 dimensions, Market, Quarter, Month.

and have 3 expressions, Like (1) sum(Target) (2) sum(Actual), (3)  sum(target) - sum(Actual)...

1st expression) i have target values for all Months Jan to Dec. and its showing perfect...

2nd expression) i have actual values only for Jan, and for other months are empty(null). because its Jan now.

problem is MY 2ND EXPRESSION sum(Actual), shows Jan values in all other columns, which are Feb, Mar, Apr and so on.

Q1) i want to see null or empty, but cant understand why it repeating same value in all columns.

Q2) my pivot table generating 1 extra column at the end , and showing some negative value in them.

see the image,

you comment would be appreciated ...!

[jonathandienst]

Hi

I suspect some problems in the data model, as it seems like MTD actual and the data in the extra column are not properly associated with your data dimensions, while Target is correctly associated. I suggest that you post a sample of your model so that this can be looked into

HTH

Jonathan

[kiranmanoharrode · Accepted solution]

Use AGGR() function to aggregate the required dimensions

[israrkhan]

kiran, i tried but could not get the result...

[israrkhan]

Hi Jonathan,

thanks and let me explain a bit more..

I have two tables

table1)

DealerCode, Item, Actual, Date,        (i created Month Quarter and Year by date field). few other column, but i am not using them.

ABC,              A,        25,     1/2/2014

ABC,              B,        35,     1/2/2014

ABC,              C,        40,    1/5/2014

ABC,             D,        45,    1/7/2014

TABLE2)

DealerCode,   Targets , Date() , i created TargerMonth, TargetYear, TargetQuarter,

ABC,                70,     1/1/2014

ABC,                80,     1/2/2014

ABC,                60,    1/3/2014

ABC,               55,    1/4/2014

ABC,                70,    1/1/2014

ABC,                80,    1/2/2014

ABC,                60,   1/3/2014

ABC,               55,    1/4/2014

Tables are linked through DealerCode.

My Dimensions are, DealerCode, TargetQuarter, TargetMonth

Expression are :

sum(Target) ... target in 2nd table

(2) sum(Actual),.( Actual in first table)

(3) sum(target) - sum(Actual)

problem: second expression repeating values.

Note: i have one target value for each month for each dealercode.

but i have multiple actual values for one dealercode, for each month.

because actual values are based on items, so each dealer has multiple items, and for those item, values on date wise.

i want to get, Sum(Actual) for all item and dates in month wise, as i added month as dimension.

if not clear please ask me...

[kiranmanoharrode]

Try below expression

=Sum(Aggr(Sum(Actual),DealerCode))

[israrkhan]

Hi Kiran ,

i tried same expression many time, without luck,

but now its working . whats the magic...!

i have not mark as "correct answer", because i have two more questions, then will close the thread.

1) any idea for last extra column, in the above image ...

2) i have one more table having only two fields, 1) MonthYear, 2) working days... one value for each MonthYear.

so to which table should i link it to, i explained my two tables in above post.

Kind Regards

Khan

[kiranmanoharrode]

Dear Israr,

check Supress Null values Option for the Quarter

or Uncheck Show Partial Sum option in Presentation tab for Quarter and Month Dimension.

for link of 2nd table Make composite key of Date for Derived dimension Month&Year

and with same name aliasing make link with your MonthYear field

Regards

Kiran Rode

+ 91 8976977897

[israrkhan]

Kiran,

i got your point for 2nd question, and that seems to work.

but for the 1st question, extra column, i unchecked "show partial sum" for all dimension , but no luck..

Kind Regards

Khan

[kiranmanoharrode]

Israr,

Try for Supress null values for all dimensions

Regards

Kiran Rode

+ 91 8976977897