Building an expression with fields from separate charts

[midwest]

Hello, 

Apologies if this has already been asked. I tried looking through the forums for a relevant post, but I wasn't able to find anything. 

I am loading two separate tables from sql server in my script. I am using those two tables in two straight table charts. These two tables can't be joined, they need to be separate for this to work.

Chart #1: Chart that uses 'service month 1' field as a dimension to indicate the 'claims' expression from Jan 1 2022 - Feb 1 2023 (service month = the month that the services occurred in, 'claims' expression = SUM(moneywereceived). This chart is indicating the money that we received for each month from Jan 1 2022 - Feb 1 2023.

Chart #2: Chart that uses 'service month 2' dimension to indicate the 'input value1' and 'input value2' from 1/1/2022 - 2/1/2023 (The service month 1 & service month 2 are technically the same data, but they are separate dimensions and need to remain as different dimensions within their chart). This chart is indicating the input values for each month from Jan 1 2022 - Feb 1 2023.

I want to use the cells from the input value 1 & input value 2 in chart #2 in an expression in chart #1.

For example, I want to take the claims for each row from 2022-01-01 - 2023-2-1 in chart #1 and divide it by the input value 1 cells for each row from 2022-1-1 - 2023-2-1 in chart #2. I've attached two images. The first one is my dashboard in its current form, depicting the separate charts. The second is an excel spreadsheet with the same data indicating the expression I want to replicate in qlikview. Is this possible to build an expression that grabs data/cells from a separate chart?

Thank you,

[rwunderlich]

" Is this possible to build an expression that grabs data/cells from a separate chart?"

No, you cannot reference data in a separate chart.  You can get data from an unconnected table, although it's generally not a good idea to do so. 

"These two tables can't be joined, they need to be separate for this to work."

Why can't these tables be joined on a common Service Month? What problem do you anticipate?

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[midwest]

Thank you for the confirming twos separate charts cannot be referenced. That is helpful to know. 

"Why can't these tables be joined on a common Service Month? What problem do you anticipate?"

The claims data from chart 1/table 1 is created with SSIS package that has an daily automatic job in sql server. The data in chart 2/table 2 is an import from a flat file that I created. The service month field in table 1 is an alias column (temporary column?), so I'm having trouble creating a join with table 2, which has service month as a column in its flat file. I understand that this is somehow possible, but I just haven't found it yet. Now that I know it's impossible to reference two separate charts, I will focus on finding how to join these two tables in a sql select query in my script. 

Thank you for the help!

[midwest]

Thank you for the confirming twos separate charts cannot be referenced. That is helpful to know. 

"Why can't these tables be joined on a common Service Month? What problem do you anticipate?"

The claims data from chart 1/table 1 is created with SSIS package that has an daily automatic job in sql server. The data in chart 2/table 2 is an import from a flat file that I created. The service month field in table 1 is an alias column (temporary column?), so I'm having trouble creating a join with table 2, which has service month as a column in its flat file. I understand that this is somehow possible, but I just haven't found it yet. Now that I know it's impossible to reference two separate charts, I will focus on finding how to join these two tables in a sql select query in my script. 

Thank you for the help!

[rwunderlich]

You will just need to rename the month columns so they are the same.  You can do that in the SQL or the load script. If you want to post your script I'll offer some suggestions if you like.

-Rob