stacked multi bar graph

[#1]

Hello All,

I really need your help in this one, the concept is normal case only, but its not working as expected.

To answer this question one needs some background knowledge on my task, that will be provided below.

Background Knowledge on Application:

1. In Simulation many users can be involved but a few (typically 2 to 3) will do actually the Simulation.

2. A Simulation has Phases, A Phase has Actions and these can be performed by users in the process of Simulation.

3. A set of Users will do a Simulation and another set of Users will observe that process.

4. Once the Simulation process is done, the users who are observing the simulation will evaluate the users who did actually.

and at the same time the faculty will also evaluate the users (who did the simulation)

I have implemented a dashboard/graph to represent the complete above scenario, by directly showing graph based on simulation id and user in the beginning. If we select any particular simulation then the graph changes o next level i.e based on users, who did that simulation actually (Here the dimension is user name). Then if we select any user then it will dig down to next level i.e based on the phases (here the dimension is phase), again if we select the phase then it will change, based on the actions (here dimension is the action).

This part id done and attached the screenshot and .qvw file as well.

Now the main task here is, I need to compare student given marks with faculty given marks (both are in different tables, i.e studentscore & facultyscore), in the same graph using stacked multi bar graph at user level, phase level, action level.

Attached the screenshot for reference (please see in the Reference sheet).

May be some of the dimensions or expression are wrong, or I am not sure actually.

I am able to do with only one level, but not in these levels (level by level, like, based on users, based on phases, and based on actions)

I really tried in many ways, but it is not working as expected, could someone of you help me in this one. Thanks in advance.

Can you let me know if you need any further information or please contact me on "narendra.papani@gmail.com"

[#2]

arvind654‌

Hi

I sent you the script in MySQL, If you need the script in either Oracle or SQL Server, let me know, I will convert and send you again.

Thanks

[MK9885]

I cannot do anything with your SQL Query.

I asked sample data cus I want to reload, remove synthetic keys and bring up a clean data model and then work on this requirement.

Probably an XL file for your data?

Thanks.

[#4]

arvind654‌

Attached the data in the excel format. Let me know if you need anything else.

[MK9885]

Thanks, I'll work on it and update you. Hopefully.

[MK9885]

Hi,

I was only able to create a clean data model for you with very minor changes for repeating fields and values.

For expressions you've to work it out by self cus there are far many options to use an expression (I don't know why you using Value) in expressions and all the expressions & dimensions have conditions. (Are you trying to supress nulls?)

About stacked bar ( you have quite a few dimensions added, hence the bar chart is taking Stacked styles automatically) remove unused dimensions and instead take them as filters. You'll have a Grouped Bar style.

I'm sorry I've tried quite enough to match your requirements but the way you've used everything needs better understanding (why those expressions are used, why only fields from Student score are used in expressions but not from faculty table?) If you have no fields added from faculty table then you won't see any reflection or bars for Faculty table.

If you trying to compare Faculty Table and Student Table use Set Analysis instead, you can supress null values as well without giving any conditions or can be done in edit script) but the expressions are not ideal for me.

Thanks for all your time & effort.

[#7]

Hi arvind654‌

Thank you so much for your valuable time spending on this task.

Can you send the data back to me , which u cleaned it.

I used 4 dimensions but out of four only one dimension will only be active the rest not as I used conditions for those dimensions. If I take them as filters instead of dimensions then will that solve my problem ?

Those expression are used because I need to calculate the phase score, action score and user score.

All those expressions are created only from StudentScore not from FacultyScore table because those are the student given score only. we can simply replicate those expressions, by creating 2 more expressions,

FacultyPhaseScore from PhaseScore (expr)

FacultyActionScore from ActionScore (expr)

The entire expression is the same, except the value field, we have to replace "value" field of StudentScore table with "val" field of FacultyScore table for the newly created expressions, after creating all these expressions then I am not able to see Subtype "Grouped" under Style tab in the Properties. There I am facing the issue.

This is the scenario.

Thanks again for your support.

[MK9885]

Hi,

I've not edited your data but only script. Cus there were too many common fields which has same values. So I renamed few fields and only made Simulation_Id as a primary key (which is common in all 4 tables).

Check the script you'll understand.

I can only suggest you to take the script which I've given (you can further modify it) but just one Key is enough for 4 tables.

You better understand the requirements than me and what expressions to be used. To work your way around you need to try with different methods and test it.

Remove the unnecessary dimensions and you might see that stacked bar will disappear and you'll have grouping bar.

FYI, I'm not an expert in Qlik but maybe someone with more expertise skills can help. I did all I could.

Thanks & no problem.

[#9]

Hi arvind654‌

Could you send me the script in a text file, as I don't have the licensed version of Qlikview personal edition to open the you have attached previously (i.e DUMMY.qvw).

[MK9885]

Hi Narendra,

Please find the attachment.

Please note: There are various ways to remove synthetic keys but I just changed the names instead of join or concatenate....with only one Primary Key as Simulation_id. It depends what you want to do with the data and when you append the records the UI also changes.

But I only changed names cus most of your fields have common names and also common values but I only found Simulation_id as common key among 4 tables.

Thanks.

[johnw]

I'm probably not completely understanding, but your Word document makes me think you're overcomplicating everything with the conditional dimensions and expressions and the aggr() functions. It looks like all you really need is a drill down group, a data model tweak, and a single simple expression.

For the data model, I concatenated the student and faculty reviews, adding a review_type field to keep straight which was which. I went ahead and let the synthetic key form on simulation and phase. There's probably a cleaner approach, but I don't know your data, so I'm not going to try to restructure that part, and it should be functional with the synthetic key. I created a drill down group of simulation_id, name, phase_id, and Action_name. I used the drill down group and the review_type as my dimensions.

Your spreadsheet has a value of 4 for every single review, so the percentage is always 100%, which makes for a very dull graph, and I don't know if it's the actual expression you need. But it seems to behave as your word document suggests.

Ah, right, you can't open a QVW. OK. Here's my script:

Reviews:
LOAD *
,'Faculty' as review_type
FROM FacultyEvaluation.xlsx
(ooxml, embedded labels, table is FacultyEvaluation)
;
CONCATENATE (Reviews)
LOAD *
,'Student' as review_type
FROM StudentEvaluation.xlsx
(ooxml, embedded labels, table is StudentEvaluation)
;

Simulation_Phases:
LOAD *
FROM Phase.xlsx
(ooxml, embedded labels, table is Phase)
;

Simulations:
LOAD simulation_id,
    user_id as SIM_UID,
    name as SIM_NAME,
    role_name
FROM SimulationRole.xlsx
(ooxml, embedded labels, table is SimulationRole)
;

To add the drill down group, Settings -> Document Properties -> Groups -> New -> I named it Group -> add fields simulation_id, name, phase_id, and Action_name -> OK -> OK.

Chart defined like this:

Dimension 1 = Group
Dimension 2 = review_type
Expression  = sum(value)/(count(Action_name)*4)