Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andre_ficken
Partner - Creator
Partner - Creator

Using Aggr() and Dual

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.

4 Replies
andre_ficken
Partner - Creator
Partner - Creator
Author

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.

sunny_talwar

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)

andre_ficken
Partner - Creator
Partner - Creator
Author

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.

sunny_talwar

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

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