6 Replies Latest reply: Mar 6, 2015 10:57 AM by Schneider Schneider RSS

    Two Dimensional Diagram with aggr function

      Hello Friends,

       

      I'm having a very curious Problem in QlikView.

      I have a number of readouts from a Database which show certain amounts of time in a different state.

      In that table there are 49 variables that describe the state, there are 7 levels of i.e the SOC and seven states of the Temperature.

      i.e the one of the fields could be named: SOC1_T1 or SOC2_T1 and so on...

      So what i get is a table full of readouts in which every i have an specific id for the object, the state of the variables and an age. There are multiple entries per Object.

      What i want to do is to plot a two dimensional diagram over all the states so i get SOC over Temperatur Histogram(Average of the maximum (or newest) value of every object).

      I tried creating to Dynamic (or syntethic) Dimensions (ValueLoop(1,7) and ValueLoop(1,8). In the formulas i reffered to them with

       

      =If(ValueLoop(1,7) = 1 and ValueLoop(1,8) = 1,
      (avg(aggr(FirstSortedValue (SOC1_T1
        ,  -age), id)) * 100))

       

      and created 49 Formulas with each state variable output.

      Problem now is: It only shows the first entry. I can replace the whole expression in the if condition with a specific number (100) and get a result. I also plotted the inner expression into a Listbox and checked wheter the result is not null.

      As soon as I delete the aggr function and just take the AVG over everything (which is not what i want). Everything works fine. When i turn back to aggr, only the first one is shown.

      Doesnt help by the way when i delete one of the dimensions, this doesnt work one dimensional either.

      Any ideas or workarounds?

      Greetings Julian

        • Re: Two Dimensional Diagram with aggr function
          Gysbert Wassenaar
          In that table there are 49 variables

          Variables or fields?


          If you're talking about fields perhaps you should use the CrossTable function to transform your data into a format that easier to work with. Can you post an example file with data like your source data?

          • Re: Two Dimensional Diagram with aggr function
            Oleg Troyansky

            The problem here lies in the combination of AGGR() and two synthetic dimensions... When AGGR is used in Chart expressions, all Chart Dimensions need to be added to the dimensions of AGGR(), or else only one of the chart lines will get calculated, and others won't - and that, if I understand correctly, is the problem here.

             

            However, Synthetic dimensions can't be added to AGGR, only static fields can be.

             

            Bottom line, this problem requires a different solution, and for that a sample document would be useful.

             

            cheers,

             

            Oleg Troyansky

            Come and learn Set Analysis and Advanced Aggregation with me at www.masterssummit.com - take your QlikView skills to the next level!

            • Re: Two Dimensional Diagram with aggr function

              Thanks for your ideas!

               

              I'm not sure how the aggr reacts to the the two dimensions. I figured since a avg(aggr()) results in a single value it should have worked...

               

              I created a sample project with random data and included a diagram with the first 3 from the 49 formulas.

               

              Thanks!

              • Re: Two Dimensional Diagram with aggr function

                Alright people, I'll try a different approach, i hope somebody can help me here:

                As i explained I have Several Bins for my Histograms, 7 Temperature * 7 SOC Bins where each value is a time in minutes that car spent in that bin + an ID for a specific car from which the Bins came and the age of the car. (IDs can be reappearing with different ages)

                 

                I created a random table to show how it would look like(image there would be seven iterations of T and SOC):

                 

                IDageSOC1_T1SOC1_T2SOC2_T1SOC2_T2
                5807141673
                38188202254
                4871921360
                593252961
                12476839042
                2582899148
                3613959729
                11294967835
                518100778436

                 

                What I'm trying to now is select the row where the age for each ID is max and read out all the bins.

                 

                Then i want to create a 7 by 7 by distinct count(ID) matrix where for each ID there is an entry with for every Temperature and an SOC.

                 

                This would enable me in qlikView to plot a 3D matrix over Temperature and SOC with only the IDs i selected.

                 

                I'd like to that in the Script. Is QlikView even able to do that?

                 

                I am able to do this in matlab by simply adding every max value to a certain field but I'm having serious troubles implementing that in qlik View.

                 

                Can anybody help me?

                 

                If thats not possible is there any other solution?

                 

                Like creating Dynamic Dimensions that replicate that order?

                 

                I was trying to use the crosstable function, but i always run into a wall at the point where i cant create a three or more dimensional matrix in QlikView.