topic Re: stacked multi bar graph in QlikView

stacked multi bar graph

Wed, 25 Nov 2020 16:16:04 GMT

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"

Re: stacked multi bar graph

hallquist_nate — Tue, 08 Nov 2016 16:17:46 GMT

I think the first thing we should do is clean up the data model. A traditional Star Schema would be well suited here, and we can concatenate the Score and Score2, and Faculty Score tables. In order to keep some order, you can create a in each table, something like this that assigns a table name to each record, so you could use a list box to select only Facutly Score records, Score records, or Score2 records. We can then use that field in Set Analysis in a chart.

My recomendation is to clean up that data model, then repost.

Re: stacked multi bar graph

MK9885 — Tue, 08 Nov 2016 16:36:59 GMT

I agree with Nate, the data model/script looks messy. It has many synthetic keys (repeating of field names). Clean the Data Model and why are you using containers for each object?

I do not think it necessary either.

Edit your script and post the qvw again, thanks.

Re: stacked multi bar graph

Thu, 10 Nov 2016 00:06:11 GMT

arvind654‌

Suppose if I remove all repeating field names (from SimulationRole, Phase, Score & FacultyScore tables) then If I select "SimulationID" field for example, the data tables are changing automatically based on selected value. But if I change those column names uniquely, does Qlikview filters the data ?? based on my selection.

Please correct me if I am wrong

Re: stacked multi bar graph

Thu, 10 Nov 2016 00:14:18 GMT

No need to concentrate on Score2 table in the script (I forgot to remove it).

But for my knowledge the data model is not messy actually, I am not getting which portion of the data model do I need to clean up. Could you please elaborate that one ?

Could you also tell me how does it will help me complete my task.

If am not clear in the original question, here is the link for the document which I need to implement:

slack-files.com/T02FXUAB9-F2ZFGQZME-4ec1868ba8

Re: stacked multi bar graph

Thu, 10 Nov 2016 00:15:06 GMT

arvind654‌

If am not clear in the original question, here is the link for the document which I need to implement:

slack-files.com/T02FXUAB9-F2ZFGQZME-4ec1868ba8

Re: stacked multi bar graph

MK9885 — Thu, 10 Nov 2016 15:27:47 GMT

Can you please also upload the sample data?

Can't really with with your data model, it has way too many synthetic keys.

Also the document which you provided has Student Score & Faculty Score as expressions for 1st chart and I do not see those expressions but only Total Score, Phase Score & Action Score.

It is achievable but requirements are not clear/data model.

Provide all the expressions you want to use

And also the dimensions you want & for which chart? There are 2 charts and I assume it is for 1st chart.

Re: stacked multi bar graph

Thu, 10 Nov 2016 18:10:25 GMT

I will upload all the required docs and sample data by evening.

Thank you.

Re: stacked multi bar graph

MK9885 — Thu, 10 Nov 2016 18:23:49 GMT

Sorry for the trouble...& thanks.

Re: stacked multi bar graph

Fri, 11 Nov 2016 23:40:37 GMT

arvind654‌

Hello

Please find the attached docs (which you asked earlier).

Sorry for the delay, let me know if you need anything else.

Thanks

Narendra

Re: stacked multi bar graph

Sat, 12 Nov 2016 06:10:13 GMT

arvind654‌

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

Re: stacked multi bar graph

MK9885 — Mon, 14 Nov 2016 21:22:06 GMT

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.

Re: stacked multi bar graph

Mon, 14 Nov 2016 23:23:45 GMT

arvind654‌

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

Re: stacked multi bar graph

MK9885 — Tue, 15 Nov 2016 14:12:37 GMT

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

Re: stacked multi bar graph

MK9885 — Tue, 15 Nov 2016 16:29:42 GMT

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.

Re: stacked multi bar graph

Tue, 15 Nov 2016 19:46:59 GMT

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.

Re: stacked multi bar graph

MK9885 — Tue, 15 Nov 2016 19:55:22 GMT

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.

Re: stacked multi bar graph

Wed, 16 Nov 2016 23:55:28 GMT

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).

Re: stacked multi bar graph

MK9885 — Thu, 17 Nov 2016 12:10:15 GMT

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.

Re: stacked multi bar graph

johnw — Mon, 28 Nov 2016 22:23:09 GMT

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)