topic Re: Dynamic field display in a chart in QlikView

Dynamic field display in a chart

Thu, 21 Jan 2016 19:25:41 GMT

Greetings,

I have a chart which displays fields(Columns) Account,Org-ID Amount, Account Amount and The Difference.

The Org-ID amount is contained in Fields labeled Jan,Feb,Mar,Apr,May,Jun,Jul, Aug,Sep,Oct,Nov and Dec.

I would like to be able to display the different Org-ID amount fields in the Chart. To expand upon that.

I would like to be able to display Account, Org-ID, Sep,Account amount and the difference.

I would like to be able to display Account, Org-ID, Apr,Account amount and the difference.

I would like to be able to display Account, Org-ID, Jan,Account amount and the difference.

I would like to be able to display Account, Org-ID, May,Account amount and the difference.

Is there any way to dynamically select a field to display in a chart?

Thanks,

Frank

Re: Dynamic field display in a chart

NareshGuntur — Thu, 21 Jan 2016 19:45:35 GMT

Sorry if I misunderstood it.

Org-ID amount is a field?

And you say "The Org-ID amount is contained in Fields labeled Jan,Feb,Mar,Apr,May,Jun,Jul, Aug,Sep,Oct,Nov and Dec."

Jan, Feb, Mar as well are fields?

Cheers,

Naresh

Re: Dynamic field display in a chart

Thu, 21 Jan 2016 20:04:01 GMT

Yes Naresh, These are the fields that I have in my document:

Account Number,

Net,

Org-ID,

Year,

Jan,

Feb,

Mar,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec

Net is the amount for the Account Number

Jan is the January amounts for the Org-Id, etc...

Re: Dynamic field display in a chart

NareshGuntur — Thu, 21 Jan 2016 20:11:21 GMT

Then you should use the function crosstable() and convert the Jan, Feb.... as values and store the respective amounts in the new field.

Cheers,

Naresh

Re: Dynamic field display in a chart

jaspsing — Thu, 21 Jan 2016 20:24:34 GMT

Hi Frank,

Please find the solution as attached. Hope it will be helpful.

if you have more column then adjust the 3 parameter in crosstable function.

Thanks

Re: Dynamic field display in a chart

Thu, 21 Jan 2016 20:29:27 GMT

I am unable to use the CrossTable wizard. I am not exactly sure how to use the function.

Re: Dynamic field display in a chart

NareshGuntur — Thu, 21 Jan 2016 20:35:52 GMT

Ok. Can you provide 1 row of dummy data which suits your case

Re: Dynamic field display in a chart

Thu, 21 Jan 2016 21:00:46 GMT

Year	Account Number	Account Amount	OrgID	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2014	0009186FIXCHG2014	0.50	00555-LOCONSTRUCT	0.04	0.04	0.04	0.03	0.04	0.04	0.04	0.04	0.04	0.05	0.05	0.05

Re: Dynamic field display in a chart

NareshGuntur — Thu, 21 Jan 2016 21:11:07 GMT

This is how you can use CrossTable

CrossTable(Month, Data, 4)

LOAD Year,

[Account Number],

[Account Amount],

OrgID,

Jan,

Feb,

Mar,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec

FROM

(ooxml, embedded labels, table is Sheet1);

and the data will be like this

Year	Account Number	Account Amount	OrgID	Month	Data
2014	0009186FIXCHG2014	0.5	00555-LOCONSTRUCT	Apr	0.03
2014	0009186FIXCHG2014	0.5	00555-LOCONSTRUCT	Aug	0.04
2014	0009186FIXCHG2014	0.5	00555-LOCONSTRUCT	Feb	0.04
2014	0009186FIXCHG2014	0.5	00555-LOCONSTRUCT	Jan	0.04
2014	0009186FIXCHG2014	0.5	00555-LOCONSTRUCT	Jul	0.04
2014	0009186FIXCHG2014	0.5	00555-LOCONSTRUCT	Jun	0.04
2014	0009186FIXCHG2014	0.5	00555-LOCONSTRUCT	Mar	0.04
2014	0009186FIXCHG2014	0.5	00555-LOCONSTRUCT	May	0.04
2014	0009186FIXCHG2014	0.5	00555-LOCONSTRUCT	Sep	0.04
2014	0009186FIXCHG2014	0.5	00555-LOCONSTRUCT	Dec	0.05
2014	0009186FIXCHG2014	0.5	00555-LOCONSTRUCT	Nov	0.05
2014	0009186FIXCHG2014	0.5	00555-LOCONSTRUCT	Oct	0.05

Re: Dynamic field display in a chart

effinty2112 — Fri, 22 Jan 2016 08:42:28 GMT

Hi Frank,

Try this and it might help you come up with a solution. Create a listbox with the field $Field (uncheck the Show System Field checkbox). This will give a list of all the field names. Select one of the month names in the list, say Nov. Using your single line of sample data a textbox with the expression =$Field will return Nov and a textbox with the expression =$(=$Field) will return the value in this field in your record 0.05.

Using the System Field $Field might be what you need here.

Good luck.

Re: Dynamic field display in a chart

Fri, 22 Jan 2016 13:04:27 GMT

Hi Jaspal,

Nothing was attached to this. Please resend.

Thank You,

Frank