How to perform aggregation similar to SUMX - Qlik Community

joshiroc · ‎2021-03-25

Hello Community,

I'm fairly new to Qlik Sense and working to recreate a dashboard I previously created in Excel Power Pivot / Power BI. I've successfully recreated most of the data model; however, I'm really stuck recreating one particular measure where I used SUMX in Power Pivot.

Here's the data model view in Qlik Sense Enterprise:

The measure I am attempting to recreate from Power Pivot it:

Adherence:=sumx( 'Project Mapping', min( [FTE.Actual] , [FTE.Allocated] ) ) / [FTE.Allocated]

If you are not familiar with DAX (and I am far from an expert), this expression sums the minimum of Actual and Allocation for each row in the 'Project Mapping' table and divides by the total allocation. [FTE.Actual] and [FTE.Allocated] are both measures with their own underlying formulas.

In Qlik Sense, I have the following expression (which is not working and returning 0 regardless of selections):

[Adherence] = sum( aggr( min( [FTE Actual] , [FTE Allocated] ) , [Project Name] ) ) / [FTE Allocated]

And here are the measures that are used within the Adherence measure, which all appear to be calculating correctly:

[FTE Actual] = [Total Hours] / (8 * [TotalWorkDays])

[TotalWorkDays] = Count({$<[Date] = {"<=$(=$(v_LastWorkedDate))"}, [DayOfWeek] = {"<5"}, [Is Holiday] = {0}, [Source] = {"Actuals"}>} [Date])

[Total Hours] = Sum([Actual Hours])

v_LastWorkedDate = 'max({$<[Actual Hours] = {"> 0"} >} [DateKey])'

[FTE Allocated] = sum( [FTE] )

Here's a screenshot of a pivot table where you can see [FTE Actual] and [FTE Allocated] are calculating correctly, but [Adherence] is returning 0:

I am really lost on why [Adherence] doesn't work. Any thoughts on what I might try or do differently?

rwunderlich · ‎2021-03-25

I think you made an error in the Min() function. You will need two Min() functions like:

RangeSum(
aggr( min( [FTE Actual] ) , [Project Name] ),
aggr( min( [FTE Allocated] ) , [Project Name] ),
) / [FTE Allocated]

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

joshiroc · ‎2021-03-25

Thanks very much for the response Rob. I am not yet able to determine if your suggestion will get me over the hurdle or not as I apparently don't understand how all of the selections are applied in conjunction with pivot tables either.

I have now unfortunately realized that[FTE Actual] is not being calculated correctly as originally thought, and digging deeper I appears the problem lies within [TotalWorkDays] calculation. I need to fix that problem first before getting back to the other expression.

What I need [TotalWorkDays] to calculate is the number of work days within a selected time period that a resource is available. You can see in the table below that [TotalWorkDays] is presently calculating the number of days a resource works on a particular project.

Here's what I know does not work as desired [TotalWorkDays] =:

Count({$<[Date] = {"<=$(=$(v_LastWorkedDate))"}, [DayOfWeek] = {"<5"}, [Is Holiday] = {0}, [Source] = {"Actuals"}>} [Date])
- In this case v_LastWorkedDate calculates as expected outside the pivot table, but within the pivot table returns the last date worked on a particular project
Count({$<[DayOfWeek] = {"<5"}, [Is Holiday] = {0}, [Source] = {"Actuals"}, [Dummy Project ID]>} [Date])

Thoughts on how to make [TotalWorkDays] behave as desired both inside and outside the pivot table?

rwunderlich · ‎2021-03-25

You don't show what the expression in v_LastWorkedDate is, but I imagine it's something like

max(Date)

When you calculate that in the chart, you are going to get max(Date) for the row dimensions, in this case Resource & Project. If you want the max value from only Resource, you will need to adjust your max date expression to use a total keyword like:

Max(Total<Resource> Date)

-Rob

joshiroc · ‎2021-03-25

The definition of v_LastWorkedDate was in the original post, and the behavior was not as desired when used in a pivot table.

However, your hint to TOTAL tipped me off a bit and I made some great strides in the right direction now.

Key learnings thus far:

TOTAL is used to disregard dimension values in tables/charts
Set expressions are used to override selections

So now using TOTAL in the v_LastWorkedDate with the fields I do not want to disregard in "<>":

v_LastWorkedDate = max(total<[Dummy Resource ID], [Month]> {<[Actual Hours] = {"> 0"}>} [DateKey])

And similarly for TotalWorkDays:

TotalWorkDays = Count(total<[Month]> {$<[Date] = {"<=$(=$(v_LastWorkedDate))"}, [DayOfWeek] = {"<5"}, [Is Holiday] = {0}, [Source] = {"Actuals"}>} [Date])

I now get a pivot table that appears to calculate those two expressions correctly:

I'm also a bit closer on the Adherence calculation, but not quite there. When the Resource dimension is expanded in the pivot table like above, Adherence calculates correctly. When I collapse the Resource dimension it's not calculating correctly and I know I need an AGGR in there somewhere but can't get it right. For example, for Resource Number 57, the correct total Adherence would be (0.25 + 0.3) / 0.95 = 57.8%.

I've tried the below expression (plus others) to no avail:

sum(aggr(Rangemin( [FTE Actual], [FTE Allocated] ),[Dummy Project ID])) / [FTE Allocated]

Thoughts again please!?!