Calculated dimension using SetAnalysis

[#1]

Hi there,

I'm trying to compare the outcome for one account number (401) in Table A with the AccountOutcomeFinal for the same account in Table B following the same date, same region and state. I'm trying to create a calculated dimension in a table containing (Date, RegionTableA, SateTableA and AccountNrTableA) from TableA using the aggr() function and set analysis to make the comparison possible. Unfortunately I'm not getting the right sum in the set analysis part and I'm not sure if this is the right way to do it. Please advise.

Many thanks

[datanibbler]

Hi CK1000qv,

I tried something similar - using set_analysis in a dimension - once before and it doesn't work. I cannot explain exactly why, but set_analysis is not suited to that purpose.

Can you describe a bit clearer what you want to achieve?

[jonathandienst]

Hi

Assuming the image is from the table view of your model, then your set analysis will select a range of values from TableA (which results in a list of possible Dates) and any value from TableB that has a Date in that list.

You need a model where the two sets are associated by more than the date alone.The construct such as StateTableA = StateTableB does not work because the set expression is evaluated once for the table, not once per row. It does not cause an error but it is ignored.

You may be able to get this working with a sum(if()) construct, but you would do better by modifying the model to include a link table containing the relevant fields, or even concatenating the one table onto the other.

HTH

Jonathan

[#4]

Hi there,

Well it's quite "simple". In Table A I have some information about different accounts with account number (1 to 500).

Example Table A:

(Date: Jan2014 / RegionTableA: Arizona / StateTableA: Texas / AccountNrTableA: 401 / Outcome: 500 USD).

And in Table B I have almost the same information but with the Final outcome.

Example Table B:(Date: Jan2014 / RegionTableB: Arizona / StateTableB: Texas / AccountNrTableA: 401 / AccountOutcomeFinal: 525 USD).

So what I need to do is to subtract the outcome from Table A from the final outcome in Table B only for the account number 401 that correspond to the same date, state and region. The rest of the account numbers will have only the outcome from Table A. My problem is how to handle the account number 401 where I need to display the difference between the outcome and the final outcome for that account. And I can't join these 2 table for different practical reasons. Many thanks.  

[#5]

Hi,

OK, I understand. Well I tried to make a unique key link by merging different dimensions but how far should I go?

Should I merge all common dimensions like the following example?

Example: Date|State|Region|AccountNr as KeyLink in both tables, but does it really works?