topic Graphic with values from 2 different lists in QlikView

Graphic with values from 2 different lists

Thu, 12 Dec 2013 12:49:14 GMT

Hi folks, i need some help here.

I have 2 sheets with data, 2 lists (OPTIONAL and OPTIONAL_DESIRED) to filter that data's and i also have a list mixing those 2 lists (let's call this 'Wanted list').

I need a graphic that shows me both selections, list 1 plus list 2, i need the graphic to do the same thing as the Wanted list does plus providing me the sum of the costs for each optional.

And also, if i select the same optional from list 1 and 2, the cost doesn't change twice.

I'm attaching my XLS and my QVW, and I've already selected some values, and as you'll be able to see, the graphic show me both but the cost is totally wrong.

Thank you in advance!

PS: you need to change the path at the script:

'FROM

[xxxx\data config.xlsx]

(ooxml, embedded labels, table is F1);'

Re: Graphic with values from 2 different lists

giakoum — Thu, 12 Dec 2013 13:28:04 GMT

This is the best I could come up with. Hard coded I am afraid...

Please add all fields in expression, I only added 3.

Re: Graphic with values from 2 different lists

Thu, 12 Dec 2013 13:47:16 GMT

Thank you so much for replying, i see what you did but that 'Optional Desired' list, that list can change according to the client, and if that changes i need to add all the fields in expression again right?

If i'm right, is there another way?

Re: Graphic with values from 2 different lists

giakoum — Thu, 12 Dec 2013 13:48:55 GMT

You can create a data island in the reload script combining both dimensions into one, and thus using the new dimension in the charts.

Re: Graphic with values from 2 different lists

Thu, 12 Dec 2013 16:16:27 GMT

Thank you, but i have no idea what you are talking about lol

Re: Graphic with values from 2 different lists

giakoum — Thu, 12 Dec 2013 17:15:35 GMT

Hi Julio.

load distinct values from optional and optional_desired into a new field and use this field as a dimension :

AllOptionalTable:

LOAD distinct

OPTIONAL as AllOptional

FROM

[xxxx\data config.xlsx]

(ooxml, embedded labels, table is F1);

AllOptionalTable:

LOAD distinct

OPTIONAL_DESIRED as AllOptional

FROM

[xxxx\data config.xlsx]

(ooxml, embedded labels, table is F2);

This will create a new field AllOptional which you can use. As this will not be connected to your data, some additional set analysis will be required in the expression. Hope this helps.

Re: Re: Graphic with values from 2 different lists

Mon, 16 Dec 2013 11:54:41 GMT

I tried to do that but i don't think i did that right and that didn't work. I'm attaching my original workbook and QVW.

What i need is to show at the 'Bar Graphic' the selection on 'AS IS' and 'OPTIONAL' without bugging my costs.

The way i did there, i'm using the same expression i used on the List to mix both selection and it works, however the costs go crazy with that.

If at the Graphic, instead of the Expression you select 'Optional', the costs are going to be right but you are only getting the selection from Optional, and i need both selections to show in there.

And i also tried to put a TOTAL, at the end, but it's not showing me anything.

Re: Re: Re: Graphic with values from 2 different lists

tresB — Mon, 16 Dec 2013 12:57:57 GMT

Hi,

Please find the attachment.

Re: Re: Graphic with values from 2 different lists

Mon, 16 Dec 2013 13:14:55 GMT

Please, use this attachments i sent last. Those are my original ones. (Project Review and data config)

And i see what you did but that 'AS IS' list, that list can change according to the client, and if that changes i need to add all the fields in expression again, am i right?

Re: Graphic with values from 2 different lists

tresB — Mon, 16 Dec 2013 13:36:16 GMT

Sorry, I am not sure about your exact requirement. Please explain the expected output.

Re: Graphic with values from 2 different lists

Mon, 16 Dec 2013 14:38:04 GMT

I need the graphic to do the same thing as the 'TO BE' list does, to show all both selections in it.

But i also need to show the 2 costs i'm working on the graphic (the red one and the blue one).

Cuz if i use the same expression i'm using on the list at the graphic, it shows what i need, which is the selection of both lists, but the costs goes crazy (if you sum them you gonna notice that).