Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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,
S
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
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.
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
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
Thanks for your help with this. A dummy qvw should be attached!
Best,
J
Please find attached
Hope this is what you are looking to do.
Best,
S
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
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
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