Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 |
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
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...
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
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
I am unable to use the CrossTable wizard. I am not exactly sure how to use the function.
Ok. Can you provide 1 row of dummy data which suits your case
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 |
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 |
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.