Using multiple alternate states in one expression

[Newuser101]

Hi, I'm attempting to create a KPI which will show the quartile that data from one state will be in, when compared against another state. Currently this is my formula (I'm yet to add the if statements to select quartiles):

Sum({[State 1]}[Field 1]) / Sum({[State 2]}[Field 1])

Field 1 is the same name across both states.

However, using this formula generates a null value. I have checked each individual formula (i.e. Sum({[State 1]}[Field 1]) )  as both master measures and as simple KPIs so can see that on an individual level they work, however, when simply put together with a "/" between there is an error.

Please could anyone help! If you just give me the way to divide that would work but if you could include the whole quartile calculation that would be even better 🙂

[asinha1991]

This should have worked but you can try this once

Sum({<[Field 1]=p({[State 1]}[Field 1])>}[Field 1]) / Sum({<[Field 1]=p({[State 2]}[Field 1])>}[Field 1])

[Newuser101]

Hi Asinha,

Unfortunately that didn't work either, what I did in the end was instead of using alternate states I simply split the data into two identical tables with prefixes for the headers and calculated everything that way - sometimes it's better to take a step back clearly!

Thanks for your help though.

[sunny_talwar]

Is this used in a KPI object or is this used in a chart where you have dimension? It might be easy to troubleshoot if you can share a sample or at least some images to see the issue?

[Newuser101]

It was used in a KPI object. I think an issue was being caused by my data having a paragraph break in the headers which confused the qlik software. I removed this then simply duplicated the data and added prefixes which now works simply and intuitively.