Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
israrkhan
Specialist II
Specialist II

Pivot Table Repeating Value

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 ...!

1.png

1 Solution

Accepted Solutions
kiranmanoharrode
Creator III
Creator III

Use AGGR() function to aggregate the required dimensions

View solution in original post

9 Replies
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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
kiranmanoharrode
Creator III
Creator III

Use AGGR() function to aggregate the required dimensions

View solution in original post

israrkhan
Specialist II
Specialist II
Author

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

israrkhan
Specialist II
Specialist II
Author

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
Creator III
Creator III

Try below expression

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

israrkhan
Specialist II
Specialist II
Author

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
Creator III
Creator III

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
Specialist II
Specialist II
Author

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
Creator III
Creator III

Israr,

Try for Supress null values for all dimensions

Regards

Kiran Rode

+ 91 8976977897