Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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)
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.
I would think so... just add an equal sign before the variable name within the dollar sign expansion
{">=$(=vYear1)<=$(=vYear5)"}