topic Re: sum() in pivot table based on absent distinct dimension in QlikView

sum() in pivot table based on absent distinct dimension

chrismtb — Fri, 05 Feb 2021 16:50:23 GMT

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

Re: sum() in pivot table based on absent distinct dimension

Frank_Hartmann — Fri, 05 Feb 2021 20:55:27 GMT

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;

Re: sum() in pivot table based on absent distinct dimension

chrismtb — Mon, 08 Feb 2021 11:31:55 GMT

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

Re: sum() in pivot table based on absent distinct dimension

chrismtb — Mon, 08 Feb 2021 12:05:03 GMT

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

Re: sum() in pivot table based on absent distinct dimension

chrismtb — Mon, 08 Feb 2021 12:05:25 GMT

*pivot table