how to write set analysis

[#1]

Hi,

I need to write set analysis to get this information in Staright table. could anyone help me how to write the set snalysis please.

Bank Account Balance = SUM(ActualAmount) from EXP.CASH_TRANSACTIONS where ActualAccountID = <allocated accountID for each individual case>
BI Account Balance = SUM(ActualAmount) from EXP.CASH_TRANSACTIONS where AccountID = <allocated accountID for each individual case>
Sum of funds held = SUM(AmountReserved) from EXP.CASH_PAYMENTPLAN where AccountID = <allocated accountID for each individual case>

here ActualAccountID is from EXP.CASH_TRANSACTIONS

       AccountID is from EXP.CASH_TRANSACTIONS

       AccountID is from EXP.CASH_PAYMENTPLAN

Thanks.

[#2]

Please can anyone help me as I don't have much idea to use set analysis.

[senpradip007]

Hi Amelia

Can you please ellaborate ypur problem as it is not meaningful.

Set Analysis:

Sum({$<YourField1={Your Value}, YourField2={Your Value} >} YourField).

[#4]

Hi,

I need to get Sum(ActualAmount) for each case and the condition shoud be ActualAccountID=AccountID

could you please let me know if I get for one column I will do it for all. and could you let me know some more clear about Sum({$<YourField1={Your Value}, YourField2={Your Value} >} YourField).

Thanks.

[#5]

It can be similar to this

=Sum({$<ActualAccountID={$(=only(AccountID))}>} ActualAmount) for your case but not sure of the Only function .

But the Set analysis expression will be similar for your case.You can try the above expression & verify whether the expected values are getting..

Thanks

[jonathandienst]

Hi

Is either ActualAccountID or AccountID a dimension on your table? If so, I am afraid that you cannot use set analysis in this case. If your dataset is not too large, you can use

Sum(If(ActualAccountID=AccountID, ActualAmount))

But this will perform poorly if your dataset is large and ActualAccountID and AccountID are in different tables.

Regards

Jonathan

[#7]

Yes you are right these two are in different tables and because of this it's performaing porrly. Could you help how to increase the performance and somehow it is getting the incorrect values also.

Thanks.

[jonathandienst]

Amelia

Hard to say without understanding your data model. Could you post more detail such as a sample model and/or table view of the model.

In general when you are dealing with large datasets, you want to transform the data into a star schema. This usually gives the best compromise between model size and performance.

Regards

Jonathan

[#9]

please find attached.