sum() in pivot table based on absent distinct dimension

[chrismtb]

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 ID	Name	Project	Week Start	SeqID	No Hours
1	Joe	A	08/02/2021	1	7
1	Joe	A	08/02/2021	2	7
1	Joe	A	08/02/2021	3	7
1	Joe	B	08/02/2021	4	7
1	Joe	B	08/02/2021	5	7
2	Adam	A	08/02/2021	1	7
2	Adam	C	08/02/2021	2	7
2	Adam	C	08/02/2021	3	7

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 ID	Name	Project	Week Start	Sum Hours
1	Joe	A	08/02/2021	21
1	Joe	B	08/02/2021	14
2	Adam	A	08/02/2021	7
2	Adam	C	08/02/2021	14

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

[Frank_Hartmann · Accepted solution]

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;

[chrismtb]

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]

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

[chrismtb]

*pivot table