6 Replies Latest reply: Feb 27, 2018 2:49 PM by Ryan Metcalf

# sum over distinct dimension

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

A very simple example of my data looks like this:

LineDateShiftMinutes
108/3/20173480
208/3/20173480

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.

• ###### Re: sum over distinct dimension

Try this

Sum(Aggr(Minutes, Shift, Date))

• ###### Re: sum over distinct dimension

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?

• ###### Re: sum over distinct dimension

Yes, I would agree

• ###### Re: sum over distinct dimension

Thank you!  You're always so helpful!

• ###### Re: sum over distinct dimension

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).

• ###### Re: sum over distinct dimension

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