topic Re: Using an expression inside another expression. in QlikView

Using an expression inside another expression.

jessica_webb — Fri, 06 Feb 2015 16:14:42 GMT

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.

Re: Using an expression inside another expression.

sunny_talwar — Fri, 06 Feb 2015 16:34:38 GMT

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,

Re: Using an expression inside another expression.

Anonymous — Fri, 06 Feb 2015 16:58:01 GMT

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.

Re: Using an expression inside another expression.

jessica_webb — Tue, 10 Feb 2015 08:13:23 GMT

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

Re: Using an expression inside another expression.

sunny_talwar — Tue, 10 Feb 2015 10:38:25 GMT

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,

Re: Using an expression inside another expression.

jessica_webb — Tue, 10 Feb 2015 11:43:30 GMT

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

Best,

Re: Using an expression inside another expression.

sunny_talwar — Tue, 10 Feb 2015 12:08:35 GMT

Please find attached

Hope this is what you are looking to do.

Best,

Re: Using an expression inside another expression.

jessica_webb — Tue, 10 Feb 2015 13:14:07 GMT

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,

Re: Using an expression inside another expression.

sunny_talwar — Tue, 10 Feb 2015 13:17:15 GMT

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,

Re: Using an expression inside another expression.

jessica_webb — Tue, 10 Feb 2015 13:21:18 GMT

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

Re: Using an expression inside another expression.

sunny_talwar — Tue, 10 Feb 2015 13:27:59 GMT

Hey Jessica,

The application you shared had Year as dimension for the stacked bar chart. That is why the stacked bar chart I made had several bar, one each for every year. Do you not want to see year as the dimension?

Best,

Re: Using an expression inside another expression.

jessica_webb — Tue, 10 Feb 2015 13:30:50 GMT

Sorry for the confusion. I'm having this problem with another stacked bar chart I'm doing and haven't found a solution yet!

I want year as a dimension (so one bar per year), but I want each bar split by +/- 80% as outlined above.

Thanks,
J

Re: Using an expression inside another expression.

sunny_talwar — Tue, 10 Feb 2015 13:40:17 GMT

See if this makes sense. Its showing % of locations that have above 80% response rate and stacked on top of it is the % of locations that have below 80% response rate. The sum of the two bars is 100%.

Let me know what you think.

Best,

Re: Using an expression inside another expression.

jessica_webb — Tue, 10 Feb 2015 13:41:42 GMT

Absolutely fantastic! Thank you so much for your help - exactly what I was looking for!

Very best wishes,

Jessica

Re: Using an expression inside another expression.

sunny_talwar — Tue, 10 Feb 2015 13:47:45 GMT

I am glad that I proved helpful.

Best,