Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- troubles in getting averages per dimensions in piv...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2013-12-19
08:55 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

troubles in getting averages per dimensions in pivot chart

Dear all,

I have a datatable which looks somewhat like the following

OrderLine | Category | Month |
---|---|---|

1111 | on time | Jan |

1112 | on time | Jan |

1113 | delay | Jan |

1114 | delay | Feb |

1115 | delay | Feb |

1116 | delay | Mar |

1117 | delay | Mar |

1118 | on time | Mar |

1119 | on time | Mar |

The end result that I would like to obtain in a pivot table in QV is the following:

Jan | Feb | Mar | Average | |
---|---|---|---|---|

delay | 33% | 100% | 50% | weighted average(33%;100%;50%) |

on time | 66% | 0% | 50% | weighted average(66%;0%;50%) |

I think the result is pretty self explanatory.

I can get this with absolute values (i.e., counting of lines)

but as soon as I try the avg() function, either with or without Dimensionality(), I either get 100% everywhere or no values.

Can you please someone help me out, it would be so much appreciated!!

thank you so much!

811 Views

1 Solution

Accepted Solutions

2013-12-19
10:22 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

If that's your definition of a weighted average then you might as well use simply *count(OrderLine)/count(total <Month> OrderLine) *as expression.

I thought you wanted the average of the month values: weighted average(33%;100%;50%). That's what my first expression calculates.

671 Views

7 Replies

Not applicable

2013-12-19
09:03 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Erik

Are you doing avg(OrderLine) ?

Chris

671 Views

Not applicable

2013-12-19
09:04 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

yeah, i also tried this one amongst other formulas.

Should that be the good one?

671 Views

2013-12-19
09:28 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

See attached qvw

Not applicable

2013-12-19
10:04 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dear Gysbert!

it is working almost perfectly; only, the total averages part is giving wrong results.

With the formula you input, the total averages per month are averaged, regardless of the number of lines.

Meanwhile I would like to have the average for the whole amount of lines, regardless of the months.

Hope I made myself clear and thanks for your input!

671 Views

Not applicable

2013-12-19
10:08 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I came up with this piece of script and it seems working as expected.

Could someone please confirm if it is valid?

if(SecondaryDimensionality()=1,

count(OrderLine)/count(total <Month> OrderLine),

sum(aggr(count(OrderLine)/count(total OrderLine),Category))

)

Thanks!

671 Views

2013-12-19
10:22 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

If that's your definition of a weighted average then you might as well use simply *count(OrderLine)/count(total <Month> OrderLine) *as expression.

I thought you wanted the average of the month values: weighted average(33%;100%;50%). That's what my first expression calculates.

672 Views

Not applicable

2013-12-19
10:52 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I admit that I have sometimes troubles in expressing what I really want/need and I perhaps take a more complicated than necessary route.

But I thought, in this case, that the "weighted average" term was the most appropriate

[...] where instead of each of the data points contributing equally to the final average, some data points contribute more than others.

so, if Feb has less line than Mar, it will count less.

But indeed, "average of the total" would have been an easier way of saying.

And indeed, the formula reported by you work as expected!

thank you

671 Views