topic Re: Calculated Dimension... based on condition... in QlikView

Calculated Dimension... based on condition...

Wed, 04 Jul 2012 09:31:37 GMT

Hi,

I have a dataset containing the ages of a bunch of people. I also have these linked to Age Groups, i.e 0-10, 11-20, 21-30 etc etc. I have multiple Age Group Types, so the age group type described previously was 10 year incremental, but i also have a 5 year incremental group (0-5, 6-10, 11-15 etc etc), plus others.

I have a chart with the dimensions AgeGroup & Gender. The expression is a simple count.

For the chart to display correctly, i need to either select a single AgeGroupType, Or, Add the equivalent set analysis into my expression. What i WANT to acheive is...

If a single AgeGroupType is NOT selected then by default use AgeGroupType X, else use whatever AgeGroupType is selected.

I've tried building an 'If' statement into my expression but it just didnt work. it was something like...

If(Count({$} Distinct [AgeGroupTypeName])=5

//Then

,Count(DISTINCT{$<AgeGroupTypeName={'Ten-year intevals'}>}EpisodeId)

//Else

,Count(DISTINCT{$}EpisodeId)

Thanks,

-Mathew

Re: Calculated Dimension... based on condition...

jonathandienst — Wed, 04 Jul 2012 09:52:10 GMT

You could try this:

If(GetSelectedCount(AgeGroupTypeName) <> 1,

Count({<AgeGroupTypeName = {'Ten-year intervals'}>} DISTINCT EpisodeId),

Count({<AgeGroupTypeName = {"$(=GetFieldSelections(AgeGroupTypeName))"}>} DISTINCT EpisodeId))

This should use 'Ten-year intervals' unless exactly one AgeGroupTypeName is selected, in whih case it should use the selecetd one.

Hope that helps

Jonathan

Re: Calculated Dimension... based on condition...

Wed, 04 Jul 2012 10:07:41 GMT

Awesome Thanks Jonathan. I was on the right track i just needed guiding.

Re: Calculated Dimension... based on condition...

Ferran_Garcia_Pagans — Wed, 04 Jul 2012 10:25:57 GMT

Hi Mathew,

I would create a table for diferent kind of age goups.

Now you can control if there is a age group type selected or not in the chart:

Finally you can use conditional view for the diferent age groups in the dimension sheet.

I used this expression.

=SubStringCount(Concat(_dimension, '|'), 'Grupo 1')

I've developed a very small example to test it. If you send me an email I can send you by email.

I found this technique in this demo:

http://eu.demo.qlik.com/detail.aspx?appName=Whats%20New%20in%20QlikView11.qvw

Regads, F.