Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Disregard the dimension in chart

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

15 Replies
Not applicable
Author

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

Not applicable
Author

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.

swuehl
MVP
MVP

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

Not applicable
Author

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

swuehl
MVP
MVP

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

Not applicable
Author

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