Count salesdays in multiple dimensions

Anonymous — Tue, 06 Dec 2016 08:55:07 GMT

Hi all,

I've been looking for a solution for the following problem in Qlikview:

In Month to Date reports, I want to compare Sales of this month with the month last year (Mont to Date). The thing is, our sector there are big fluctuations in Sales between days and on top of that, some but not all, branches are open in weekends. So for good comparison, I want to be able to show the number of "Salesdays".

We have defined a Saleday as a day with at least € 1000 sales on a given day, per branch.

We have a number of branches in different countries. A number of those are being managed by a Salesmanager, who are in turn managed by Salesdirectors.

This means that a Salesmanager who governs 5 branches must have 5000 euro Sales on that day to count as a SaleDay.

In charts, I am using a variable so that when you click a Sales Director, you see the managers he governs, and when you click a manager, you can see his branches.

Unforunately I am not getting the right results for Salesdays on all levels. Currently I am using

COUNT(DISTINCT IF( AGGR(SUM([Sales])>(1000*COUNT(DISTINCT Hub.Name)),%Base) AND InMonthToDate(Date,vMaxDate,0), Date))

In this expression Hub.NAME are branches and %Base is the key of the salestable where every date&Hub.Name combination is a distinct key.

This expression works for the lowest level (branches) but does not get the right results on the Sales director and Sales manager level. It still seems to work with the 1000 euro, instead of, for instance, 5000 for a manager with 5 branches.

Am I not ussing AGGR correctly?

Re: Count salesdays in multiple dimensions

Gysbert_Wassenaar — Tue, 06 Dec 2016 09:03:59 GMT

1000*COUNT(DISTINCT Hub.Name)

This will always be calculated inside the aggr at the level that you specified namely %Base. If you want to calculate it at a different level like Salesmanager then you need to add the test for it at that level.

Re: Count salesdays in multiple dimensions

vinieme12 — Tue, 06 Dec 2016 09:06:39 GMT

can you post sample app/ data with desired output?

topic Count salesdays in multiple dimensions in QlikView

Count salesdays in multiple dimensions

Re: Count salesdays in multiple dimensions

Re: Count salesdays in multiple dimensions