Disregard the dimension in chart

[#1]

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

[erichshiino]

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

[#3]

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

[#4]

If required I could pull together a simple QVW for you to see tomorrow?

[#5]

Here's an example QVW file. Thanks for your help

[#6]

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

[#7]

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.

[#8]

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.

[swuehl · Accepted solution]

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

[#10]

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