Help Please

[James_B]

hi All,  I am having issues working out what I am doing wrong, below is the expression I'm using to calculate the cover for all departments which works but when i filter each department to make sure they have enough cover it still shows the same overall figure. how can i link the result to what i have filtered?

=avg({<[Cost Ctr]={'1234'}>} CoverLine)+avg({<[Cost Ctr]={'1235'}>} CoverLine)+avg({<[Cost Ctr]={'1245'}>} CoverLine)+avg({<[Cost Ctr]={'1235'}>} CoverLine)

[GaryGiles]

Does the field [Cost Ctr] represent the department and is that the field you are filtering on?

Is so, add an * before each = in your set analysis, like this:

=avg({<[Cost Ctr]*={'1234'}>} CoverLine)+avg({<[Cost Ctr]*={'1235'}>} CoverLine)+avg({<[Cost Ctr]*={'1245'}>} CoverLine)+avg({<[Cost Ctr]*={'1235'}>} CoverLine)

It will create an Intersection between the value specified and the selection made in the filter.  So, you choose '1235', that [Cost Ctr] will remain.

[James_B]

yes that's correct the field I'm filtering is [Cost Ctr] and yes it represents the department. i will add this expression Thank you.

[James_B]

unfortunately this seems not to be working, when I filter a specific cost center the value is - 

[GaryGiles · Accepted solution]

Okay, let's start by rewriting your expression with the following:

=sum(aggr(avg({<[Cost Ctr]={'1234','1235','1245','1235'}>} CoverLine), [Cost Ctr]))

(Not sure why '1235' is repeated.  Was that supposed to be a different [Cost Ctr]?)

That will give you the same results as your expression with multiple avg functions.

What behavior you looking for via your filter?  Are you wanting to select [Cost Ctr] outside of the ones listed or will your selections be within that group?

=sum(aggr(avg({<[Cost Ctr]*={'1234','1235','1245','1235'}>} CoverLine), [Cost Ctr]))

will isolate a [Cost Ctr] selected within the group and return 0 if the selection is outside the group.

[James_B]

Thank you very much this worked brilliantly.

[James_B]

Hi Gary, I have a bit of a Problem, the above Expression has  worked with regards to Cost Center filtering but unfortunately when I select a specific Date the calculation doesn't display the correct data. 

What would i need to add to the expression to incorporate the date filter? ( to only show data for that date)

[GaryGiles]

I'm not sure I understand your question because that expression, as written, should react to other filters.  If you filter on a date, it would show the results of the set analysis only for rows associated with that date.  Can you be more specific about why you feel the results are incorrect?

[James_B]

Thank you for your quick response.

The data in the excel sheet when selecting a date doesn't match the data displaying in Qlik i have double checked the Expression to see if I made any faults but looks good, thought I would need to include the date in the expression but as you stated it should already include that filter so the error must be elsewhere.