Hi,
I have a problem which seems kind of complex to me, and I do not find a solution...
In the attached qvw you see a table 1, thats my data. ID is for example a customer which has a certain type. Each type has a certain factor (always the same factor for one type).
In table 2, the data are grouped by year and type, as formula I use
(- a count of the IDs per year and type)
- a count of the IDs multiplied with the factor for the specific type (for example in 2009 there are two customers with type AB, the factor for type AB is -50, so my result is (2*-50) -> -100. I used max(factor) since it doesn't seem to work at all without an aggregation here.
Table 3a contains only records where the factor of the types are positive. The value I need in the end here is the last line (Gesamt -> Total), so that I get the sum of "(Count of IDs per Type) * Factor of the Type" over all types, per year. Here comes my first problem: I do not get correct summarized values. It should look like the first table in the excel-screenshot below the tables.
Table 3a contains the same, just for negative values. Here I don't get a summary at all, it should like the second table of the excel-screenshot.
The end target of the whole thing is the graph in the excel-screenshot:
- One line for the summarized line for positive factors, one value per year (the line called "Positives")
- One line for the summarized line for negative factors, one value per year (the line called "Negatives")
- One line for the average/sum of all values, one value per year (the line called "Average")
So what I actually need is a way to create a graph as shown, produced by the data in table 1, the steps between are just debugging steps.
If you could help me with that, it would be absolutely great!
Thanks and kind regards,
Martin
