Expression issue in the tooltip.

[Anuhya_kotha]

Hi all, I am using the below expression to calculate the count of work centers where the max capacity of the work center is greater than the capacity of that particular week. I want to display the count of workcenters in the kpi.

 Sum({<[Year Period]=>}Aggr(if(Sum({<Week={'$(vMaxCapacityWeek)'},[Year Period]=>}[Start Quantity]) >
((Sum({<KPI_Filter={'Capacity'},
%DateKey={">=$(=vCapacityHistoryMinDateKey)<=$(=vCapacityHistoryMaxDateKey)"},
[Year Period]=>}[Start Quantity])
/
Count({<%DateKey={">=$(=vCapacityHistoryMinDateKey)<=$(=vCapacityHistoryMaxDateKey)"},[Year Period]=>} distinct [Year-Week]))*1.25),1,0),[Work Center]))

[Scotchy]

Here's some possible points to examine.

To display the count of work centers in a KPI, where the maximum capacity of the work center is greater than the capacity of a particular week, your Qlik Sense expression seems to be on the right track. It's using Aggr to aggregate at the work center level, and if to check the condition for each work center. However, it's quite complex and might be difficult to debug or maintain.

Let's break down your expression for clarity and suggest any potential improvements:

1. Your Current Logic:

   • Inner If Condition: Compares the sum of [Start Quantity] for a specific week (vMaxCapacityWeek) with 1.25 times the average capacity over a specified date range.
   • Aggr Function: Aggregates this comparison at the [Work Center] level.
   • Sum Outside Aggr: Sums up the count of work centers meeting the condition.

2. Improvements and Checks:

   • Variable Definitions: Ensure that the variables vMaxCapacityWeek, vCapacityHistoryMinDateKey, and vCapacityHistoryMaxDateKey are correctly defined and evaluated in the context of your expression.
   • Data Model: Confirm that your data model supports this level of aggregation and the relationships between [Year Period], [Start Quantity], [Work Center], etc., are correctly defined.

3. Simplified Version (if applicable): If possible, consider simplifying the expression by pre-calculating some of these aggregates in the script load. Complex expressions can be hard to debug and may affect performance.

4. Using Your Expression in a KPI Object:

   • Simply use this expression in the KPI chart object.
   • Ensure that the KPI object's properties and dimensions are set up to properly reflect the context in which this expression should be evaluated.

5. Debugging Tips:

   • Test in Parts: Break down the expression and test each part separately. For example, first check the calculation of average capacity, then the sum for the vMaxCapacityWeek, and finally the aggregation logic.
   • Use Text Object for Debugging: Display the intermediate values (like the calculated average capacity) in text objects for debugging.

6. Performance Consideration:

   • This expression might be computationally intensive. Monitor the performance, and if it's slow, consider optimizing your data model or the expression itself.

Remember, complex expressions like this can be sensitive to the specifics of your data model and the context in which they're used. If this doesn't work as expected, you might need to further tweak the expression or adjust your data model accordingly. If you encounter specific issues or errors, feel free to provide more details for a more targeted solution.