Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dynamic field display in a chart

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

1 Solution

Accepted Solutions
NareshGuntur
Partner - Specialist
Partner - Specialist

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
20140009186FIXCHG20140.500555-LOCONSTRUCTApr0.03
20140009186FIXCHG20140.500555-LOCONSTRUCTAug0.04
20140009186FIXCHG20140.500555-LOCONSTRUCTFeb0.04
20140009186FIXCHG20140.500555-LOCONSTRUCTJan0.04
20140009186FIXCHG20140.500555-LOCONSTRUCTJul0.04
20140009186FIXCHG20140.500555-LOCONSTRUCTJun0.04
20140009186FIXCHG20140.500555-LOCONSTRUCTMar0.04
20140009186FIXCHG20140.500555-LOCONSTRUCTMay0.04
20140009186FIXCHG20140.500555-LOCONSTRUCTSep0.04
20140009186FIXCHG20140.500555-LOCONSTRUCTDec0.05
20140009186FIXCHG20140.500555-LOCONSTRUCTNov0.05
20140009186FIXCHG20140.500555-LOCONSTRUCTOct0.05

View solution in original post

10 Replies
NareshGuntur
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

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

NareshGuntur
Partner - Specialist
Partner - Specialist

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

jaspsing
Contributor II
Contributor II

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

Not applicable
Author

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

NareshGuntur
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

YearAccount NumberAccount AmountOrgIDJanFebMarAprMayJunJulAugSepOctNovDec
20140009186FIXCHG20140.5000555-LOCONSTRUCT0.040.040.040.030.040.040.040.040.040.050.050.05
NareshGuntur
Partner - Specialist
Partner - Specialist

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
20140009186FIXCHG20140.500555-LOCONSTRUCTApr0.03
20140009186FIXCHG20140.500555-LOCONSTRUCTAug0.04
20140009186FIXCHG20140.500555-LOCONSTRUCTFeb0.04
20140009186FIXCHG20140.500555-LOCONSTRUCTJan0.04
20140009186FIXCHG20140.500555-LOCONSTRUCTJul0.04
20140009186FIXCHG20140.500555-LOCONSTRUCTJun0.04
20140009186FIXCHG20140.500555-LOCONSTRUCTMar0.04
20140009186FIXCHG20140.500555-LOCONSTRUCTMay0.04
20140009186FIXCHG20140.500555-LOCONSTRUCTSep0.04
20140009186FIXCHG20140.500555-LOCONSTRUCTDec0.05
20140009186FIXCHG20140.500555-LOCONSTRUCTNov0.05
20140009186FIXCHG20140.500555-LOCONSTRUCTOct0.05
effinty2112
Master
Master

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.