Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to use this expression:
=Only({<ATYPE={'VALUETYPE'},NUM_FROM={"<=1"},NUM_TO={">=1"}>} NUM_RESULT)
This is working fine, NUM_RESULT is the right value that I'm expecting from a between check (1 is between NUM_FROM and NUM_TO).
Now, I want to replace this 1 with a formula like this:
=avg({<BTYPE={'ANOTHERTYPE'},YEAR=P(Year)>} VALUE_PERC)
(I'm using this fomula in a pivot column, I can use also the column alias as field)
it works as a new pivot column, but If I replace the 1 with this, it doesn't work. I tried also with variables but it doesn't work.
I tried:
=Only({<ATYPE={'VALUETYPE'},NUM_FROM={"<=$(vartest)"},NUM_TO={">=$(vartest)"}>} NUM_RESULT)
where vartest is
avg({<BTYPE={'ANOTHERTYPE'},YEAR=P(Year)>} VALUE_PERC)
Thank you, best regards.
Riccardo
May be try this expression:
Aggr(If(RANGE_TYPE = DIM_TYPE and VALUE >= RANGE_FROM and VALUE <= RANGE_TO, RANGE_VALUE), DIMENSION, RANGE_TO, RANGE_VALUE)
Do you have equal (=) before Avg in the variable? Try adding that and see if that helps?
What do you mean with 'doesn't work'?
Do you expect the average to be calculated per dimension line? Then this won't work indeed.
Yes, I would like that the average will be calculated at dimension line......... How can I do this?
Thank you, regards.
Riccardo
Yes, I tried it.
It doesn't work for every lines.. It seems doesn't work at dimension-line.
Can I use a Calcolated Column ?
Set analysis is evaluated once per chart, not per dimension line, so you can't easily use set analysis here.
Start with advanced aggregation, something like
=Only(
{<ATYPE={'VALUETYPE'},NUM_FROM={"<=1"} >}
Aggr(
If( NUM_TO >= avg({<BTYPE={'ANOTHERTYPE'},YEAR=P(Year)>} VALUE_PERC), NUM_RESULT)
, YourChartDimension1, YourChartDimension2, ... , NUM_TO
)
)
Replace YourChartDimensionX with your chart dimension fields.
I am not 100% sure about the set expressions used in the inner and outer aggregation functions, so this may need to be adapted, too.
In general, it's easier to answer these questions knowing the data model and context,so if you still have issues,please post a small sample QVW.
Hi, thank you for reply. I'm going to check it and try it.... meanwhile I uploaded a little example.
Thank you, best regards.
Riccardo
May be try this expression:
Aggr(If(RANGE_TYPE = DIM_TYPE and VALUE >= RANGE_FROM and VALUE <= RANGE_TO, RANGE_VALUE), DIMENSION, RANGE_TO, RANGE_VALUE)
You can start with an expression like
=Only( Aggr( If( Sum(VALUE) >= RANGE_FROM AND Sum(VALUE) <= RANGE_TO AND RANGE_TYPE = DIM_TYPE, RANGE_VALUE), DIMENSION, RANGE_TYPE, RANGE_VALUE))
And another approach using INTERVALMATCH in the script
DIMFACT:
LOAD DIMENSION,
VALUE,
DIM_TYPE
FROM
[DimFact.xlsx]
(ooxml, embedded labels, table is DIMFACT);
RANGEVALUES:
LOAD RANGE_TYPE AS DIM_TYPE,
RANGE_FROM,
RANGE_TO,
RANGE_VALUE
FROM
[RangeValues.xlsx]
(ooxml, embedded labels, table is VALUESRANGE);
JOIN
IntervalMatch(VALUE, DIM_TYPE)
LOAD RANGE_FROM, RANGE_TO, DIM_TYPE
RESIDENT RANGEVALUES;
LEFT JOIN (DIMFACT)
LOAD DIM_TYPE, VALUE, RANGE_VALUE
RESIDENT RANGEVALUES;
DROP TABLE RANGEVALUES;