Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $400 before Dec 31st: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
rmmetcalf
Contributor III
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
sunny_talwar

Try this

Sum(Aggr(Minutes, Shift, Date))

View solution in original post

6 Replies
sunny_talwar

Try this

Sum(Aggr(Minutes, Shift, Date))

timsworldroamer
Partner - Contributor III
Partner - Contributor III

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
Luminary Alumni
Luminary Alumni

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
Contributor III
Contributor III
Author

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

rmmetcalf
Contributor III
Contributor III
Author

Thank you!  You're always so helpful!