topic Re: Including/Excluding set modifier dimension in Selections in QlikView

Including/Excluding set modifier dimension in Selections

Anonymous — Fri, 12 Aug 2016 16:23:53 GMT

Hello,

Is it possible to include or exclude selections based on the Set Modifier dimension (even if the dimension forces a value from all three values in said dimension) ?

Here is the variable used in my expression

Sum({$<[Resource Type] = {'Retained'},[Internal Project]={'External'}>} If([#Hours Billed] = 0, [#Hours Worked], [#Hours Billed]) * #Rate)

Sum({$<[Resource Type] = {'Contractor'},[Internal Project]={'External'}>} If([#Hours Billed] = 0, [#Hours Worked], [#Hours Billed]) * #Rate)

Sum({$<[Resource Type] = {'Employee'},[Internal Project]={'External'}>} If([#Hours Billed] = 0, [#Hours Worked], [#Hours Billed]) * #Rate)

The dimension values I'm concerned with including in my selection are the Resource Types. Now, I would prefer to use a "Fade out" mechanism in the chart that fades out any of the resource types not selected, but I'm having trouble understanding the logic. I found a post that explains how to do this (Background color conditional formatting), but I'm not sure it will work in my case because my chart is using set analysis and including all values for the Resource Type dimension.

Any ideas? Could I achieve this chart (It can have aggregated values for every type of resource per dimension (consultant name)) using another approach? Maybe not using Set Analysis?

Chart for your reference ~

Thanks!

Channing

Re: Including/Excluding set modifier dimension in Selections

sunny_talwar — Fri, 12 Aug 2016 16:49:38 GMT

Would you be able to share a sample to play around with the few ideas I have?

Re: Including/Excluding set modifier dimension in Selections

marcus_sommer — Fri, 12 Aug 2016 16:51:36 GMT

Your original expression could be look like:

Sum({$<[Resource Type] = {'Retained', 'Contractor', 'Employee'},[Internal Project]={'External'}>} If([#Hours Billed] = 0, [#Hours Worked], [#Hours Billed]) * #Rate)

and for only including the selected values of resource type you could use:

Sum({$<[Resource Type] = p([Resource Type]),[Internal Project]={'External'}>} If([#Hours Billed] = 0, [#Hours Worked], [#Hours Billed]) * #Rate)

- Marcus