Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Average sales monthly sales in a year per salesman

Hello,

I'm preparing a chart with monthly sales per salesman for one desired year.

I have a xls table with invoice lists from 2013 and 2014 like this:

Date of sales | Salesman | Amount |

2014-01-05   | 1               | 500 |

2013-06-09   | 3               | 600 |

2013-10-12   | 1               | 300 |

2013-10-23   | 6              | 200 |

2014-01-21   | 3              | 950 |

2013-06-29   | 1              | 720 |

2013-10-24   | 3              | 100 |

2014-12-21   | 4              | 280 |

2012-02-06   | 7              | 900 |

2013-07-19   | 6              | 400 |

Real file has over 9 000 lines.

I want to prepare combo chart with sales per month for 2014 year with Filter Pane to filter sales per salesman (which is easy).

Hardest part for me is to put a line on chart with average sales per month per salesman (2014 sales/12 months/Number of salesman) which disregards any selections of Filter Pane.

I want to put another line with average sales per salesman for each month.

It should look like this.

Bez tytułu.jpg

I tried many different versions but when I select salesman number on Filter Pane it changes average lines also.

I use this expression in measurments:

Sum({1} {$<Year={2014}>} Total [Amount])/12/5  - for average monthly sales from whole year per salesman

Sum({1} {<Rok={2014}>} [Wartość netto])/5  - for average monthly sales per salesman

Any ideas?

1 Solution

Accepted Solutions
MarcoWedel

Sum( {1<Year={2014}>} Total [Amount])/12/Count({1<Year={2014}>} Distinct Salesman)


Sum( {1<Rok={2014}>} [Wartość netto])/Count({1<Year={2014}>} Distinct Salesman)

View solution in original post

5 Replies
swuehl
MVP
MVP

Try maybe

Sum( {1<Year={2014}>} Total [Amount])/12/5  - for average monthly sales from whole year per salesman

Sum( {1<Rok={2014}>} [Wartość netto])/5  - for average monthly sales per salesman

And why do you divide by 5?

MarcoWedel

Sum( {1<Year={2014}>} Total [Amount])/12/Count({1<Year={2014}>} Distinct Salesman)


Sum( {1<Rok={2014}>} [Wartość netto])/Count({1<Year={2014}>} Distinct Salesman)

Not applicable
Author

I'm dividing by 5 because I have 5 salesman.

Not applicable
Author

Works perfect. Thank you very much!

You saved me a lot of time and trouble.

MarcoWedel

You're welcome

Regards

Marco