topic Re: Using Aggr() and Dual in QlikView

Using Aggr() and Dual

andre_ficken — Fri, 08 Dec 2017 21:25:31 GMT

Hi There,

I would like to create the following horizontal stacked bar chart over this year and the 4 years prior:

per year, show a horizontal stacked bar that consists of 3 categories:

category3: the total revenue generated by customers where the revenue >= 0 and <20k

category2: the total revenue generated by customers where the revenue >=20k and <50k

categorie1: the total revenue generated by customers where the revenue >=50k

The second horizontal stacked bar chart that I would like to create over the same period as the previous chart

is per year in the same categories the number of unique customers have have been generating the revenue per category.

I have been trying quite a few things, but I have not hit the jackpot yet...

My dimensions are:

- the 5 years requested

- the 3 categories, using the Aggr() and Dual#

I am having difficulties with grouping the years and the categories and to get the count and sum of the expressions to match with the categories. I am using set analysis a lot since the dashboard has a static year/previous year and from/to date settings.

So my set analysis starts with {1< and not {$<

I need some simple guidelines to get the aggr and dual right and the sum and count in the expressions.

Re: Using Aggr() and Dual

andre_ficken — Sat, 09 Dec 2017 12:06:23 GMT

Additional information: I have been able to get my stacked bar graph working for 1 year (2017):

My dimensions are: [Selection Year] eg. 1 year only. and the categories <=20000, 20000-50000 and >50000 by using aggr and dual:

=Aggr(

if(Sum( {$< [Record_Type] = {Invoice'},

[CompanyType] -= {'*Internal*'},

[ItemType] -= {'Travel','Materials','*Intern*'},

[Year] = {2017}

>}[Amount]) <= 20000, Dual('0-20k',1),

if(Sum( {$< [Record_Type] = {'Invoice'},

[CompanyType] -= {'*Internal*'},

[ItemType] -= {'Travel','Materials','*Intern*'},

[Year] = {2017}

>}[Amount]) <=50000, Dual('20-50k',2),

if(Sum( {$< [Record_Type] = {'Factuur'},

[CompanyType] -= {'*Internal*'},

[ItemType] -= {'Travel','Materials','*Intern*'},

[Year] = {2017}

>}[Amount]) >50000, Dual('50+k',3))))

, CustCode

My Expression:

Sum( {$< Record_Type={'Invoice'}, [CompanyType] -= {'*Internal*'},

[ItemType] -= {'Travel','Materials','*Intern*'},

[Year] = {2017}

>} DISTINCT CustCode)

This displays correct info on the count of customers as well as sum of total invoice amount.

What I now need is to expand this graph from 1 selection year to 5 years (Selection Year - 4)

So that I get 5 stacked bars with 3 categories. Hope you can get me some advise how to get this done.

Re: Using Aggr() and Dual

sunny_talwar — Sat, 09 Dec 2017 14:59:58 GMT

Did you try to add Year to the Aggr() function and see if that works

=Aggr(

if(Sum( {$< [Record_Type] = {Invoice'},

[CompanyType] -= {'*Internal*'},

[ItemType] -= {'Travel','Materials','*Intern*'},

[Year] = {">=2013<=2017"}

>}[Amount]) <= 20000, Dual('0-20k',1),

if(Sum( {$< [Record_Type] = {'Invoice'},

[CompanyType] -= {'*Internal*'},

[ItemType] -= {'Travel','Materials','*Intern*'},

[Year] = {">=2013<=2017"}

>}[Amount]) <=50000, Dual('20-50k',2),

if(Sum( {$< [Record_Type] = {'Factuur'},

[CompanyType] -= {'*Internal*'},

[ItemType] -= {'Travel','Materials','*Intern*'},

[Year] = {">=2013<=2017"}

>}[Amount]) >50000, Dual('50+k',3))))

, CustCode, Year)

Expression

Sum( {$< Record_Type={'Invoice'}, [CompanyType] -= {'*Internal*'},

[ItemType] -= {'Travel','Materials','*Intern*'},

[Year] = {">=2013<=2017"}

>} DISTINCT CustCode)

Re: Using Aggr() and Dual

andre_ficken — Sat, 09 Dec 2017 16:03:33 GMT

Can I replace the 2013 and 2017 by variables too??

Would that look like: {">=$(vYear1)<=$(vYear5)"} ??

Thanks for the first part, I will give that a try tomorrowmorning.

Re: Using Aggr() and Dual

sunny_talwar — Sat, 09 Dec 2017 16:09:10 GMT

I would think so... just add an equal sign before the variable name within the dollar sign expansion

{">=$(=vYear1)<=$(=vYear5)"}