Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Set analysis conflict

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

Tags (2)
1 Solution

Accepted Solutions

Re: Set analysis conflict

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({....})

7 Replies

Re: Set analysis conflict

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.

Not applicable

Re: Set analysis conflict

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

Not applicable

Re: Set analysis conflict

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

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.

Not applicable

Re: Set analysis conflict

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

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({....})

Not applicable

Re: Set analysis conflict

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