sum over distinct dimension

[Anonymous]

I would like to know the expression for summing over a distinct dimension.

A very simple example of my data looks like this:

Line	Date	Shift	Minutes
10	8/3/2017	3	480
20	8/3/2017	3	480

My complete data set has two years of dates and shifts in it across many lines.  When the line is running, minutes is 480, otherwise it is 0.

I want to sum the minutes of a distinct shift.  I have tried SUM(DISTINCT <ShiftNumber+Date> Minutes) but it doesn't work.  I know that COUNT(DISTINCT ShiftNumber+Date) gives me the correct answer of 1.

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(Minutes, Shift, Date))

[timsworldroamer]

Ryan,

Is your straight table going to contain just 2 dimensions and 1 expression - Date, Shift and Minutes? If so all you need would likely be Sum(Minutes). Otherwise try aggr(nodistinct sum(Minutes), Shift, Date).

[pablolabbe]

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others

[Anonymous]

Thanks Sunny!  I forgot about the AGGR function.  I'll get to testing this in the chart I'm using.  The dimension in the chart is Employee, so I'm guessing I'll need to change the aggregation to Sum(Aggr(Minutes, Shift, Date, Employee)) so that each employee's measure shows up in the chart.  Is this correct?

[sunny_talwar]

Yes, I would agree

[Anonymous]

Thank you!  You're always so helpful!