topic Re: Set analysis conflict in QlikView

Set analysis conflict

Tue, 17 Feb 2015 14:48:51 GMT

Hi all,

I am trying to predict the total headcount of a Business over the next few years. To do this we've segmented the workforce and said that some people are going to be affected by Revenue Growth while others will be affected by Digital Revenue.

As part of this we are also assuming that places like HR will be affected by the headcount in the rest of the business (not including themselves).

To calculate the Headcount changes in HR we have to calculate the headcount Growth Rate in the rest of the Business. This has left me with a variable where I sum everything where {<[DemandDriver] - = {'Headcount'}>}. This variable works on it's own and gives me the correct value. The variable is called vNoHRGrowthRate

So to calculate the headcount Growth in HR I end up with this formula:

=Sum({<DemandDriver={'Headcount'}>}[HeadcountSum]*
pow(((($(vNoHRGrowthRate)-1)*([cHeadcount])), YearIndex))

Where [cHeadcount] is a field where the is value dependent on the particular [HeadcountSum] is being Summed.

[YearIndex] is a coded inline table where 2015=0, 2016=1 etc.

The problem is that the set analysis in Red is clashing with the set analysis in the vNoHRGrowthRate variable and is returning a null value. (The section in Red says Sum everything where it IS equal to headcount and in the variable it additionally tells it to sum everything where DemandDriver is NOT equal to headcount)

If I replace the variable with its numerical value then the formula works - i can't leave it like this though as the values change dynamically.

I just need to put in some notation that tells it to ignore the previous set analysis before calculating the value of vNoHRGrowthRate.

Is there a way to do this?

Thanks and kind regards

Mel

Re: Set analysis conflict

rubenmarin — Tue, 17 Feb 2015 14:54:29 GMT

Hi Mel, your expression can work like this?:

=Sum({<DemandDriver={'Headcount'}>}[HeadcountSum])*
pow(((($(vNoHRGrowthRate)-1)*([cHeadcount])), YearIndex)

I separated the first Sum, so each operation has his own set analysis.

Re: Set analysis conflict

Tue, 17 Feb 2015 14:59:07 GMT

Hi Ruben,

This is Tom, I'm working with Mel.

I'm afraid that doesn't work as ([cHeadcount]) is a field value that changes depending on which [HeadcountSum] is being used.

In other words, if I close off the first part of the formula as you show, then it doesn't know which [cHeadcount] value to use and returns a null.

Thanks,

Tom

Re: Set analysis conflict

Tue, 17 Feb 2015 15:07:47 GMT

Hi Ruben,

I'm afraid that doesn't work as ([cHeadcount]) is a field value that changes depending on which [HeadcountSum] is being used.

In other words, if I close off the first part of the formula as you show, then it doesn't know which [cHeadcount] value to use and returns a null.

Thanks,

Mel

Re: Set analysis conflict

rubenmarin — Tue, 17 Feb 2015 15:15:06 GMT

And using Aggr to calculate each [HeadcountSum] value?:

=Sum(Aggr(Sum({<DemandDriver={'Headcount'}>}[HeadcountSum])*
pow(((($(vNoHRGrowthRate)-1)*([cHeadcount])), YearIndex), [HeadcountSum]))

A sample document will be nice to make tests.

Re: Set analysis conflict

Tue, 17 Feb 2015 15:50:05 GMT

Thanks so much for your effort Ruben, I've tried that as well but I'm afraid I still receive a null value.

I'm afraid I can't really distribute this either - hands tied.

As a bit of extra information:

- If i calculate the value of vNoHRGrowthRate separately and then plug in that value (it's approx 0.99) then I receive the right answers.

It seems all I need to do is to calculate that value separately and then plug it in.

Any ideas?

Thanks again for all your help.

Mel

Re: Set analysis conflict

rubenmarin — Tue, 17 Feb 2015 15:59:25 GMT

Hi, to give another guess with this last option, you can start the variable with an equal sign, then it should return the result of the variable,ie change variable value from:

Sum({....})

to:

=Sum({....})

Re: Set analysis conflict

Tue, 17 Feb 2015 16:02:40 GMT

Yes!!

That's done it.

Thanks Ruben - I knew deep down it would be something simple like that. Thank you for your help.

I'll mark that as correct.

Mel