topic Re: Disregard the dimension in chart in QlikView

Disregard the dimension in chart

Thu, 17 Nov 2011 14:19:01 GMT

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

Disregard the dimension in chart

erichshiino — Thu, 17 Nov 2011 14:41:44 GMT

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

Re: Disregard the dimension in chart

Thu, 17 Nov 2011 15:19:44 GMT

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

Re: Disregard the dimension in chart

Thu, 17 Nov 2011 15:42:23 GMT

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

Re: Disregard the dimension in chart

Fri, 18 Nov 2011 09:46:04 GMT

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

Re: Disregard the dimension in chart

Fri, 18 Nov 2011 12:14:50 GMT

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

Re: Disregard the dimension in chart

Fri, 18 Nov 2011 12:30:00 GMT

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.

Re: Disregard the dimension in chart

Fri, 18 Nov 2011 13:40:12 GMT

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.

Re: Disregard the dimension in chart

swuehl — Fri, 18 Nov 2011 14:04:40 GMT

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

Re: Disregard the dimension in chart

Fri, 18 Nov 2011 14:41:06 GMT

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

Re: Disregard the dimension in chart

Fri, 18 Nov 2011 15:03:11 GMT

Thanks Stefan, this is great - thanks very much for your help.

Stefan / Shyam

What would the most efficient way to handle this type of requirement?

I've noticed on your answer Stefan you're using an If statement, which I'm led to believe take a lot of processing (and should be avoided by all accounts)

Creating a new table would makes things simpler - We actually had that designed in the SQL (if I'm reading your code right Shyam) but we found it became too large for QV to handle..... Originally One record of the fact would contain both the numerator and the denominator column, MeasureID, timestamp, other keys, ...

Just to give you an idea there were around 70 mil rows for a three month period. What we also found was most of that was non event information still creating a row. For example % Break Time = Break Time / Logged on Time. Typically, during their shift, agent were not on "Break" but still "Logged On" so non events were being recorded due to the fact "Logged On" had a value. This seemed even more wasteful as "Logged On" was used as a denominator for several measures similar to % Break Time.

This is why we were looking at splitting out measures into their component parts which would make it simple to remove 0 values whilst still collecting the "Logged On Time" as a separate component. this reduced the overall table size dramatically

Reading a Aggr explanation is says I'm creating a "Temp Table" in some respects. Or at least I could view its operation that way.......???

Thanks very much for your advice guys !! I appriecate your help with this

Rich

Re: Disregard the dimension in chart

Fri, 18 Nov 2011 15:12:40 GMT

Just thought of another point which may or may not be useful.

In the final dashbaord the majority of charts would not be affected by this issue.

Most charts would just be looking at a single measure over time (for example), these charts I can generate the expressions easily using set expression and hardcoding the ID which they need to reference to.

Re: Disregard the dimension in chart

swuehl — Tue, 22 Nov 2011 17:50:48 GMT

Richard,

you are probably right with the cost of if() function and aggr() (and you are again right, it is something like a "Temp table" indeed), so my solution might be a bit academic in your case.

I'm not sure if Shyam's solution won't do the job in your case and I think you don't need to replicate every column, but I am not sure if I understood you situation well enough (especially the part with the break time and the logging in).

In general, I would step back and ask myself if I really need a DB contained description of my expressions which would allow me to use the expressions as dimension (like in your example).

As you mentioned, most objects in your dashboard only need specific measures, so are probably going to hardcode them.

(And if we are talking about hardcoding, I don't want to imply literally repeating the expressions on different places, you could / should use e.g. variables to abstract the expression some way).

If you do need to keep the expression definition in your DB, we might need to take a closer look at your data model to see if we could simplify things (if we want to go for a data model based solution).

Regards,

Stefan

Re: Disregard the dimension in chart

Fri, 02 Dec 2011 13:46:05 GMT

Hi Stefan

Sorry for the delay in responding.... I worked your formulas into my dashboard and loaded it with the full dataset. There is a slight delay on calculations although I still think its an acceptable wait time for results and for resolving that problem I'm very greateful.

Just to elaberate on why this was an issue to begin with:

Over time my organisations measures change and although the calcuations are different they are still the same measures
Measure only apply to cetain Role or Service type (ie one meausre may not be applied to all)

To combat this the SQL team developed a "job" which reads new data as it comes though then on a separate table writes the results of the measures components along with reference keys.... So calulations are completed well before QV is on the scene but this helps QV by not having complicated expressions handling changes to calculations over time or measure that only apply to certain dimensions

So now, in QV, rather than hard code a measure =(Sum(ColumnA)+Sum(ColumnB))/Sum(ColumnC)

For the first slide in the example the code is:

=$(=only({$<MeasureRoleName= ,MeasureChannelName=,MeasureID={16}>} [QlikView Numerator Calculation]))

$(=only({$<MeasureRoleName= ,MeasureChannelName=,MeasureID={16}>} [QlikView Denominator Calculation]))

The numberator and denominator calculation its reading basically look like this: Sum({$<Component={10}>} PersonRoleLoginPerformanceValue)

The second slide gives another slant on the data but as a single measure has to be selected the simple formular above can be reused, but you don't specify the MeasureID.

The final slide shows the chart with the issue. You could image a Manager using this view to look at an individual or team, it has all the KPI's / Measure there with time periods running across the top so from that point of view is really useful. Of course I could have put the measures across the top and perhaps time as a dimension and got similar results, true although (unfortunatly) as an organisation this orientation isn't what they're used to. This is where the orignal formula I had wouldn't work.........

........ but yours did! 🙂

Thanks again,

Richard

Re: Disregard the dimension in chart

swuehl — Fri, 02 Dec 2011 13:59:17 GMT

Richard,

thanks for the update and I'm glad the performance is not as bad as feared.

Just a note to your last paragraph, with a pivot table, you can swap the orientation of expressions and dimensions, but a pivot table might show different issues (e.g. with sorting).

Have a nice weekend,

Stefan

Re: Disregard the dimension in chart

Fri, 02 Dec 2011 14:19:02 GMT

Thansk Stefan,

Yes, that could have been a great option although you're righ, sorting is something I wanted to keep to help with understanding Top and Bottom performers..... its a bit of a pandora's box sometimes....

Have a good weekend too.

Rich