Using an expression inside another expression.

[jessica_webb]

Hi, hoping someone might be able to come up with a solution to what may well be a very simple problem that I'm having...

I have created an expression for my straight table, which gives me a percentage of respondents within an area giving a particular response. The expression is:

Count (DISTINCT{1<Year={'2014'}, Type={1}, Response={1}>} RespondentID)

/ Count (DISTINCT{1<[Academic Year]={'2014'}, Type={1}>} RespodentID)

In the straight table, this is split by a 'Location' dimension, so that for each location I get my percentage.

I now want to create a 100% stacked bar chart with two sections - locations where over 80% of respondents gave that response; locations where under 80% of respondents gave that response.

So far, I have created a new variable called 'vRES1' with my original expression as the definition.

I have then tried to use that variable within an expression for my stacked bar chart, to no avail! I have:

Count (DISTINCT{<IF($(vRES1)>=0.80)> Location) /

Count (DISTINCT {<$(vRES1)={'>=0'}>} Location)

Qlikview is happy with the expression, but I get no data in my stacked bar chart!

Any help or suggestions gratefully received!

Many thanks.

[sunny_talwar]

I would do something like this in the script itself. But there might be a way to do this in the expression.

In the script, I would use Group by statement and calculate the percentage and use that information to create a new field name (something like If(abc% > 80%, 1, 0). This way I can easily count the instance when its above 80%.

HTH

Best,

S

[Anonymous]

Jessica,

Depending on how exactly your variable looks like, you may need $(=vRES1).

But more important, you need to aggregate by Location, otherwise you have count within count which is not going to work.

[jessica_webb]

Thanks for your help sunindia.

Would you be able to explain how to do this in the script? Do I just use my original expression somewhere? Have had a look on other discussions for an explanation of this, but not found anything so far.

Thanks again,

Jessica

[sunny_talwar]

Would you be able to provide with some dummy data and I can show you how something like this could be done in the script.

Best,

S

[jessica_webb]

Thanks for your help with this. A dummy qvw should be attached!

Best,

J

[sunny_talwar]

Please find attached

Hope this is what you are looking to do.

Best,

S

[jessica_webb]

Hi,

Thank you so much - this appears to be on the right track. Have tried putting this into my qv document and the main problem I'm having is that my stacked bar chart should be a 100% stacked chart...

Any further thoughts?

Thanks again,

J

[sunny_talwar]

So instead of seeing the number, you want to see % of count?

+ places where the % cannot pe calculated (Salisbury), should they be included in the count also (i.e. below 80% count)?

Let me know and we can make a tweak in the application

Best,

S

[jessica_webb]

So for all locations, a certain % will have above 80% and the rest will have below 80%.

I would like my stacked bar chart to show for all locations (as a group, not individually) what % got 80%+ and what % of locations got 80%-

So it should be just one bar split into two sections which total 100% together.

All locations should be included in this aggregated total regardless - in my data I actually have about 250 locations.

Thanks,
J