15 Replies Latest reply: Dec 2, 2011 9:19 AM by Richard Pearce RSS

    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

        • Disregard the dimension in chart
          Erich Shiino

          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

              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

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

                  • Re: Disregard the dimension in chart

                    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
                        Stefan Wühl

                        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

                            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

                                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
                                  Stefan Wühl

                                  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

                                      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

                            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

                                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

                                    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