# Qlik Sense App Development

New Contributor II

## Add calculated measure to "Concat/If/Aggr/Rank" function

Hi experts,

I have created a text box that shows my 3 best product sales increases for a given month compared to the previous month and this for a specific region.
My calculation is the following :

-----------------------------

concat(

IF(

aggr(

Rank(

(Avg({\$<[Region]={"A"}>}[Sales])-Avg({\$<[Region]={"A"}, Month = {"\$(=Date(AddMonths(Month, -1), 'MMM-yy'))"}>} [Sales]))),

[Product])<=3,

[Product] & chr(10)))

-----------------------------

This works fine but does not show the increase in sales.
Any idea how I could show this calculation in here :

[Product] & ' (' & ??? &')' & chr(10)

Cheers

Tags (2)
1 Solution

Accepted Solutions MVP

## Re: Add calculated measure to "Concat/If/Aggr/Rank" function

May be this

Concat(

If(Aggr(Rank((Avg({\$<[Region]={"A"}>}[Sales]) - Avg({\$<[Region]={"A"}, Month = {"\$(=Date(AddMonths(Month, -1), 'MMM-yy'))"}>} [Sales]))), [Product])<=3,

[Product] & '(' & Num(Aggr((Avg({\$<[Region]={"A"}>}[Sales]) - Avg({\$<[Region]={"A"}, Month = {"\$(=Date(AddMonths(Month, -1), 'MMM-yy'))"}>} [Sales])), Product), '#.#%') & ')'), Chr(10))

7 Replies MVP

## Re: Add calculated measure to "Concat/If/Aggr/Rank" function

May be this

Concat(

If(Aggr(Rank((Avg({\$<[Region]={"A"}>}[Sales]) - Avg({\$<[Region]={"A"}, Month = {"\$(=Date(AddMonths(Month, -1), 'MMM-yy'))"}>} [Sales]))), [Product])<=3,

[Product] & '(' & Aggr((Avg({\$<[Region]={"A"}>}[Sales]) - Avg({\$<[Region]={"A"}, Month = {"\$(=Date(AddMonths(Month, -1), 'MMM-yy'))"}>} [Sales])), Product) & ')'), Chr(10))

New Contributor II

## Re: Add calculated measure to "Concat/If/Aggr/Rank" function

Thanks Sunny, this works.
And in case my sales would be a %, how can I format it to have it shown as % instead of 0,...... ? MVP

## Re: Add calculated measure to "Concat/If/Aggr/Rank" function

May be this

Concat(

If(Aggr(Rank((Avg({\$<[Region]={"A"}>}[Sales]) - Avg({\$<[Region]={"A"}, Month = {"\$(=Date(AddMonths(Month, -1), 'MMM-yy'))"}>} [Sales]))), [Product])<=3,

[Product] & '(' & Num(Aggr((Avg({\$<[Region]={"A"}>}[Sales]) - Avg({\$<[Region]={"A"}, Month = {"\$(=Date(AddMonths(Month, -1), 'MMM-yy'))"}>} [Sales])), Product), '#.#%') & ')'), Chr(10))

New Contributor II

## Re: Add calculated measure to "Concat/If/Aggr/Rank" function

Hi Sunny,

Your suggestion worked fine although there is a small mistake in your formula.

Concat(

If(Aggr(Rank((Avg({\$<[Region]={"A"}>}[Sales]) - Avg({\$<[Region]={"A"}, Month = {"\$(=Date(AddMonths(Month, -1), 'MMM-yy'))"}>} [Sales]))), [Product])<=3,

[Product] & '(' & Num(Aggr((Avg({\$<[Region]={"A"}>}[Sales]) - Avg({\$<[Region]={"A"}, Month = {"\$(=Date(AddMonths(Month, -1), 'MMM-yy'))"}>} [Sales])), Product), '#.#%') & ')'), Chr(10))

If you can adapt your post, I can mark it as "correct". 🙂

Thanks again!

(Sorry for the autocorrect on your name) MVP

## Re: Add calculated measure to "Concat/If/Aggr/Rank" function

Name is Sunny.... I am glad you were able to get it fixed.... Please close the thread by marking the correct and helpful responses.

Best,

Sunny

Highlighted
New Contributor II

## Re: Add calculated measure to "Concat/If/Aggr/Rank" function

Hi Sunny, see my updated post. MVP

## Re: Add calculated measure to "Concat/If/Aggr/Rank" function

Sure 