Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a chart where the Dimension is MeasureID which relates to a FACT and reference table. Within the reference table there is also a field which relates to the denominator MeasureID contained within the same tables.
So for example MeasureID 1 would be Sum({<MeasureID={1}>}Value) / Sum({<MeasureID={30}>} Value
This works fine apart from charts where MeasureID is the dimension and I can't then add the new MeasureID that I need, ie Sum({<MeasureID=,MeasureID={30}>} Value
At this point I'm at a loss what to do / try next.
Can anyone help?
Thanks
Richard,
I think breaking the link is a good idea (or creating a data island to draw against?), and I think you need to go with aggr() instead of the set expression.
Please check attached (the denominator expression looks a bit ugly, I think we can improve that).
Hope this helps,
Stefan
Hi,
I`m not sure if I got your problem.
When you make MeasureID={30} in set analysis, you disregard the selection in the field and force it to be 30.
If you want to disregard the dimension in the chart, you can try to use total.
It will be: Sum({<MeasureID={1}>}Value) / Sum({<MeasureID={30}>} total Value)
In this case, in all the lines, you will not consider the MeasureID value in the line and will, at the same time, force all of them to be 30.
If this is not the result you want, please send us a sample of your data (or include a small table in your post) so we can understand it a little more.
Hope this helps,
Erich
Hi Erich,
TOTAL did help although I've not fully resolved the issue. I've attached an exported table to help explain things.
I've set "Denominator Calculation 1" to your previous answer Sum({<MeasureRole.MeasureID={14}>} Total Value)
which, as you said, has applied the same value to all lines of the table
In Denominator Measure ID column I've simply used =only(MeasureRole.LinkedMeasureID)
This brings back from the reference table the MeasureRole.MeasureID for the Denominator.
My next thought would be to put that into a dollar sub so I'd end up with something like this:
Sum({<MeasureRole.MeasureID={ $(=only(MeasureRole.LinkedMeasureID)) }>} Total Value)
I though this would resolve the problem but there's still an issue as you can see from "Denominator Calculation 2"
Thanks
Richard
If required I could pull together a simple QVW for you to see tomorrow?
Here's an example QVW file. Thanks for your help
Hi Richard,
It should work just fine without the set analysis. Something like this in expression- sum(Value)/sum(total Value) and MeasureID across dimension.
Hope this helps.
Cheers,
Shyam
Hi Shyam, thanks for your reply.
That would give me the total for all the measures (26396) and I need it to use the LinkedMeasureID to detirmin which MeasureID to calculate.
So on the Example QVW:
MeasureID 1 would be: 3140 / 3256
MeasureID 2 would be: 2632 / 3256
Etc
MeasureID 5 would be: 3256 / 5464
etc
On the example MeasureID 1 - 4 Value are divided by the Value of MeasureID 5
MeasureID 5 - 9 Value are divided by the Value of MeaureID 10
MeasureID 10 Value is divided by the Value of MeaureID 8
Each MeasureID is a component of a calulation and can be reused as either a numerator or a denominator and the reference table is first defining the measure name, its numerator ID and what it needs to divided by.
here's another example of the same problem.
On this I've removed the link between tables and on the example I've placed the expected Set Analysis for each formula. I've tried a few combinations to get it working but with no success.
Richard,
I think breaking the link is a good idea (or creating a data island to draw against?), and I think you need to go with aggr() instead of the set expression.
Please check attached (the denominator expression looks a bit ugly, I think we can improve that).
Hope this helps,
Stefan
Hi Richard,
You might have to change the data model slightly. Something like:
Fact:
LOAD MeasureID,
Date,
Value
FROM
[file];
Dim:
LOAD MeasureID,
LinkedMeasureID,
MeasureName
FROM
[file];
//Tmp:
left join(Fact)
load Distinct
LinkedMeasureID as MeasureID,
LinkedMeasureID as Link
Resident Dim;
Test:
load
*,
if(Link=MeasureID,Value) as LinkedValue
Resident Fact;
drop table Fact;
drop field Link;
New:
load
LinkedValue as LValue,
MeasureID as LinkedMeasureID
resident Test;
Now have sum(Value)/sum(LValue) in your expression and dimension as -MeasureID & Name.
Cheers,
Shyam