Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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
correction the data is coming into the data model with the negative numbers - it is just not displaying these in the data model
*pivot table