Two Dimensional Diagram with aggr function

[#1]

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

[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?

[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!

[#4]

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!

[Gysbert_Wassenaar]

I don't understand yet what you're trying to show in your histogram, but it will probably be a lot easier with a different data model. See attached qvw.

[#6]

Unfortunaly i only have the qlikView personal Edition, i will try to get a look on your file on another Computer next week, how did you modify the data?

Im trying to get the average value from the maximum value over each object in a 3D Diagram.

So what i see at the end is my 49 Values plotted in a 3D Diagram (SOC: 7 Temperature: 7 -> 7x7)

Something like this: (random example from wikipedia) the z would be the average from all maximas the y the temperature and the y the SOC.

[#7]

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):

ID	age	SOC1_T1	SOC1_T2	SOC2_T1	SOC2_T2
5	80	7	14	16	73
3	81	88	20	22	54
4	87	19	21	3	60
5	93	25	2	9	61
1	24	76	83	90	42
2	5	82	89	91	48
3	6	13	95	97	29
1	12	94	96	78	35
5	18	100	77	84	36

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.