Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
chrismtb
Contributor III
Contributor III

sum() in pivot table based on absent distinct dimension

Hi,

I am trying to build a pivot table for my users to show a summary of time booked to projects.

my source is similar to the below:

User IDNameProjectWeek StartSeqIDNo Hours
1JoeA08/02/202117
1JoeA08/02/202127
1JoeA08/02/202137
1JoeB08/02/202147
1JoeB08/02/202157
2AdamA08/02/202117
2AdamC08/02/202127
2AdamC08/02/202137

 

as you can see there are potentially multiple lines for one person against one project in 1 week with the same hours, the only difference being the SeqID will change.

I need my output to look like: 

User IDNameProjectWeek StartSum Hours
1JoeA08/02/202121
1JoeB08/02/202114
2AdamA08/02/20217
2AdamC08/02/202114

 

The output is excluding the SeqID column, however when I do this and set my expression to =sum(Hours) I get the wrong values out.

I have considered using the distinct however as my hours are the same i will only return 7.... I have looked through other posts and the consensus is that the issue is due to the output not having distinct enough information and therefore multiplying the values however i need this summary to exclude the unique lines and provide an accurate aggregation.

 

Does anyone have any idea what formula i can use?

 

Thanks

 

 

1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

Maybe like this:

straight table:

 

 

dim: User ID, Name, Project, Week Start

expression: sum(aggr(sum([No Hours]),[User ID],Name,Project,[Week Start]))

 

 

 

or on script level:

 

 

tmp:
LOAD [User ID], 
     Name, 
     Project, 
     [Week Start], 
     SeqID, 
     [No Hours]
FROM
[https://community.qlik.com/t5/QlikView-App-Development/sum-in-pivot-table-based-on-absent-distinct-dimension/td-p/1780589]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

left join

Final:
Load  [User ID],Project,sum([No Hours]) as SumHours Resident tmp Group by [User ID],Project;

 

 

 

 

 

 

View solution in original post

4 Replies
Frank_Hartmann
Master II
Master II

Maybe like this:

straight table:

 

 

dim: User ID, Name, Project, Week Start

expression: sum(aggr(sum([No Hours]),[User ID],Name,Project,[Week Start]))

 

 

 

or on script level:

 

 

tmp:
LOAD [User ID], 
     Name, 
     Project, 
     [Week Start], 
     SeqID, 
     [No Hours]
FROM
[https://community.qlik.com/t5/QlikView-App-Development/sum-in-pivot-table-based-on-absent-distinct-dimension/td-p/1780589]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

left join

Final:
Load  [User ID],Project,sum([No Hours]) as SumHours Resident tmp Group by [User ID],Project;

 

 

 

 

 

 

View solution in original post

chrismtb
Contributor III
Contributor III
Author

Thanks Frank -

I have gone down the script route but have just one question... In certain circumstances there is a negative value in the No Hours column (a user messing up their time booking).

When this does happen my load sum/aggregation seems to ignore these negative values - is this a known thing and what can I do to get round it?

Thanks

Chris

chrismtb
Contributor III
Contributor III
Author

correction the data is coming into the data model with the negative numbers - it is just not displaying these in the data model

chrismtb
Contributor III
Contributor III
Author

*pivot table