Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rmmetcalf
New Contributor III

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.

1 Solution

Accepted Solutions

Re: sum over distinct dimension

Try this

Sum(Aggr(Minutes, Shift, Date))

6 Replies

Re: sum over distinct dimension

Try this

Sum(Aggr(Minutes, Shift, Date))

timsworldroamer
New Contributor III

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

pablolabbe
Valued Contributor II

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

rmmetcalf
New Contributor III

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

rmmetcalf
New Contributor III

Re: sum over distinct dimension

Thank you!  You're always so helpful!

Community Browser