topic Re: how to write set analysis in QlikView

how to write set analysis

Wed, 25 Sep 2013 11:49:00 GMT

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.

Re: how to write set analysis

Wed, 25 Sep 2013 12:11:58 GMT

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

Re: how to write set analysis

senpradip007 — Wed, 25 Sep 2013 12:18:25 GMT

Hi Amelia

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

Set Analysis:

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

Re: how to write set analysis

Wed, 25 Sep 2013 12:35:49 GMT

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.

Re: how to write set analysis

Wed, 25 Sep 2013 13:59:49 GMT

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

Re: how to write set analysis

jonathandienst — Wed, 25 Sep 2013 14:13:06 GMT

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

Re: how to write set analysis

Wed, 25 Sep 2013 14:39:49 GMT

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.

Re: how to write set analysis

jonathandienst — Wed, 25 Sep 2013 14:46:33 GMT

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

Re: how to write set analysis

Wed, 25 Sep 2013 14:50:53 GMT

please find attached.