Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
ldekoning
Contributor II
Contributor II

Count salesdays in multiple dimensions

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?

2 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
vinieme12
Champion II
Champion II

can you  post sample app/ data with desired output?