Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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 III
Champion III

can you  post sample app/ data with desired output?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.