Indirect set analysis

[skaredovs]

Hi everybody,

This new feature "Indirect set analysis" in QV9 is really qool think. I started to use it and cannot solve one thing.

I have the data like this:

Amount ReportLine Line Dev1 Dev2
10 A1 A
20 A2 A
15 A A
21 B1 B
23 B2 B
C C A B

So, the target is to make the pivot table, where the dimension is ReportLine, expression is sum(Amount), but when Dev1 exists, then expression should calculate these amounts, where ReportLine is corresponded to the value of Dev1 devided by Dev2 (something like Lookup).

I'm trying to do it using Indirect set analysis and only script is working, but not giving me right result is this. Actually it gives right result only if I make the selection ReportLine=C.

if(len(Dev1)>0,
 sum({$<ReportLine = P ({1<Line={"$(=maxstring(Dev1))"}>} >} Amount/
 sum({$<ReportLine = P ({1<Line={"$(=maxstring(Dev2))"}>} >} Amount,
sum(Amount))

Thanks in advance.

Konstantins

[mongolu]

your syntax isn't right. you;ve missed a ")"and the "TOTAL".

this should work:

if(len(Dev1)>0,
sum({$<ReportLine = P ({1<Line={"$(=maxstring(Dev1))"}>} ReportLine ) >} TOTAL Amount) /
sum({$<ReportLine = P ({1<Line={"$(=maxstring(Dev2))"}>} ReportLine ) >} TOTAL Amount),
sum(Amount)
)

works also without the "ReportLine" before de TOTAL.

"If the field in the element function is omitted,
the function will return the possible values of the field
specified in the outer assignment."

[skaredovs]

Hi Mongolu,

Thanks for advise. But unfortunately this script is working only in one subtotal case.
I have attached example with many subtotals. I need to see it in pivot or straight table, as shown in example. The main question is how to return the value of the current dimension to indirect set analysis string (in place of {"$(=maxstring(Dev1))"})?

Something like this script, which is not working:

if(len(Dev1)>0,
 sum({$<ReportLine = P({1<Line={Dev1}>} )>} total Amount)/
 sum({$<ReportLine = P({1<Line={Dev2}>} )>} total Amount),
sum(Amount))

Any other solution?

Best regards,
Konstantins

[mongolu]

Well, I must say i don't know.

I've tried some options, but it won't work.

Using the value of the dimension Dev1(or 2) into the Set ... seems the syntax is wrong or maybe it can't be done this way.

But I'll say to you this (what a friend of mine gave me in this moments): think out of the box!

Maybe the abbordation is not quite the good one. Maybe you should think at another approach to reach the data.

Sorry,

Mihai

[Anonymous]

Dear Konstantins,

Have you found the solution to the question :
how to return the value of the current dimension to indirect set analysis string ?

I've got the same issue.
In a crosstab like :

Dim Indic
A =formula
B =formula
C =formula

how in the "formula" could I get in a set analysis the value of "Dim" of the current line ?
I've tried
$(=Dim)
$(=concat(Dim,','))
$(=only(Dim))
and fieldvalue, P(), …

The main issue is that a set analysis is "out of the crosstab", and know only of the selections.

A formula like =concat(Dim,',') get the dim value (A or B, or C), but the same in a set analysis get the whole list : A,B,C for the concat part.

=SUM({$<Dim2={$(=concat(Dim1,','))}>} X)
will return SUM({$<Dim2={A,B,C}>} X)
but I would like to get SUM({$<Dim2={A}>} X) when on the A line and SUM({$<Dim2={B}>} X) when on th B line.

Any idea ?