Alternate state and null fields?

[#1]

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

[swuehl]

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

[#3]

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

[swuehl · Accepted solution]

Maybe like this?

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

[#5]

Yes - that did the trick! Thanks

[#6]

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!

[#7]

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

Any solution found yet?

Thanks

[#8]

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!