How to Refer to Expression in ValueLoop

[Anonymous]

Dear All,

Let say I have a simple ValueLoop as below as my Dimension:

ValueLoop(1,3)

Then in my expression, It will be as below:

Pick(ValueLoop(1,3),

Sum(DebitAmount),

Sum(CreditAmount),

Sum(DebitAmont)-Sum(CreditAmount)

)

May I know instead of putting the calculation in the 3rd pick valueloop, is there anyway I can do the calculation by referring the result of pick 1 and pick 2 as below:

Pick(ValueLoop(1,3),

Sum(DebitAmount),

Sum(CreditAmount),

ValueLoop1 - ValueLoop2 (What script should be used here?)

)

Hope to get advice from experts here.

Thanks in advance!

[vinieme12]

i don't think it works with valueloop

I suggest you load a dummy table  like below

DummyDIM

Rowno() as DIM

Autogenerate(3);

and then change expression to below

Pick(DIM,

Sum(DebitAmount),

Sum(CreditAmount),

Sum(DebitAmount) - Sum(CreditAmount)       

)

Also to answer your question (What script should be used here?)

You cannot directly refer something that is evaluated in the same expression

[Kushal_Chawda]

You need to write expression for 3rd pick value loop Sum(DebitAmont)-Sum(CreditAmount) because you are individually evaluating expression for each value.

You would have been able to do it in expression,

for eg you have two expression like

1) Sum(DebitAmount)

2) Sum(CreditAmount)

Now you can create 3rd expression as, column(1)-column(2)

[Anonymous]

Hi Vineeth,

Thank you for you reply. Let me try to look into the possibility of loading the dummy table.

On top of that, is there any method you can introduce? because the development I involved in has a lot similar scenario, and my worry is there will be too much dummy table and caused the future maintenance of the qvw will be very complicated.

[Anonymous]

Hi Kushal,

Thank you for the reply. Do you mean that for my 3rd ValueLoop I must write Sum(DebitAmont)-Sum(CreditAmount) ?

Actually my concern is the 3rd expression or even the subsequent expression will be even longer. The expressions are just samples. But in fact the real expression I'm currently using is way much longer. If I really have no other choice but to write Sum(DebitAmont)-Sum(CreditAmount) as my 3rd ValueLoop, let say I discover the 1st valueloop expression is wrong, means I need to modify by 1st and 3rd valueloop, this will possibly caused tougher future maintenance.

Hope there are other solution that you can suggest?

Thanks again for your effort in replying.

[vinieme12]

I don't know what you are trying to create, if you can share a snapshot or sample of what you are trying to achieve it'll be easier to assist.

[Kushal_Chawda]

Thank you for the reply. Do you mean that for my 3rd ValueLoop I must write Sum(DebitAmont)-Sum(CreditAmount) ?

Yes, You need to write the entire expression. For the maintenance point of view, you can create variable instead on front end

vExpression1 :Sum(Debit)

vExpression2: Sum(Credit)

Note : Do not use "=" in variable expression

Now in chart you can use below expression

Pick(ValueLoop(1,3),

$(vExpression1) ,

$(vExpression2),

$(vExpression1) - $(vExpression2)

)

Using this approach, you just need to change the expression of vExpression1 or vExpression2 , so that vExpression3 will automatically be using the same formula that you have changed.

Hope this solve your problem