topic Re: Alternate state and null fields? in QlikView

Alternate state and null fields?

Wed, 14 Nov 2012 10:58:27 GMT

Hello,

I have a case where some of the rows in my fact table isn't connected to all dimensions:

This results in an unexpected behaviour when using alternate state to sum sales:

The alternate state doesn't include sales from the rows with null values. With no selections I expected the alternate state to have the same result as the default state has as this is how it behavies when there are no null fields in the fact table. Is this behaviour correct and if so why?

Attached is an example qvw.

Cheers

// Fredrik

Re: Alternate state and null fields?

swuehl — Wed, 14 Nov 2012 13:07:26 GMT

The problem is not the alternate state, which you can see if you use

=Sum({Alternate_1} Sale)

The problem is how you are using the set modifier, you see the problem already when using the default state:

=Sum({$ <

Year = Year,

Vegetable = Vegetable,

Fruit = Fruit

> } Sale)

You are making selections in Year, Vegetable and Fruit, so you are losing the records where these fields are NULL.

Not sure what you want to achieve, but maybe try something like this to copy over your selections to the alternate state:

=Sum({Alternate_1 <

FactKey = p($::FactKey)

> } Sale)

Regards,

Stefan

Re: Alternate state and null fields?

Thu, 15 Nov 2012 10:26:53 GMT

Hello,

I want the alternate state to follow all selections in the default state except from the field Car. This works when there are no null fields. I tried playing around with P modifier but don't get that behaviour and FactKey = p($::FactKey) gives me all fact rows in the alternate state. Still lost - any other suggestions?

// Fredrik

Re: Alternate state and null fields?

swuehl — Thu, 15 Nov 2012 10:42:35 GMT

Maybe like this?

=Sum({Alternate_1 <FactKey = p({$<Car=>} $::FactKey) > } Sale)

Re: Alternate state and null fields?

Thu, 15 Nov 2012 18:57:19 GMT

Yes - that did the trick! Thanks

Re: Alternate state and null fields?

Fri, 04 Jan 2013 14:01:50 GMT

I´m facing a similar problem, but the solution doesn´t fit it.

I need to use the selection in the default state for some filters, so I applied the syntax "field= $:: field". However one of them contains null values that have to be considered in the calculation.

For example:

Sum( {State<Category = $::Category, Type= $::Type>} sales)

Where there are sales with null Type values. I have to take into consideration both null values (excluded by Alternate State) and selections the user may make.

Please, help!

Re: Alternate state and null fields?

Wed, 06 Feb 2013 13:57:15 GMT

I'm facing the same problem as you, Gabriela.

Any solution found yet?

Thanks

Re: Alternate state and null fields?

Fri, 08 Feb 2013 19:32:08 GMT

Strohhammer,

I figured out that when we equal the fields, the Alternate State filters registers using them. In my case, there were null values for some fields, so Qlikview was ignoring these registers. In order to solve the problem, I had to ascribe "N/A" values to nulls. Once all registers were filled out, numbers were correct.

Hope it helps!