Dynamic title - issue with % calculation

Daryn — Mon, 19 Jun 2023 10:41:40 GMT

Hi all,

I have a messy dynamic title, and I can not get the last part of it working correctly,

= 'Our top 3 customers last month were '
& FirstSortedValue ([RGNAM], aggr(Rank(sum({<[FKDAT.autoCalendar.MonthsAgo] ={1},[ZBOTT_UMSATZ.VKORG] = {'UK01'}, VTWEG = {"BG","DP","EU","MO","MT","OD"}>}[NETWR]))=1,[RGNAM]))
& ' , '
& FirstSortedValue ([RGNAM], aggr(Rank(sum({<[FKDAT.autoCalendar.MonthsAgo] ={1},[ZBOTT_UMSATZ.VKORG] = {'UK01'}, VTWEG = {"BG","DP","EU","MO","MT","OD"}>}[NETWR]))=2,[RGNAM]))
& ' & '
& FirstSortedValue ([RGNAM], aggr(Rank(sum({<[FKDAT.autoCalendar.MonthsAgo] ={1},[ZBOTT_UMSATZ.VKORG] = {'UK01'}, VTWEG = {"BG","DP","EU","MO","MT","OD"}>}[NETWR]))=3,[RGNAM]))

& ' with combined sales of '

& If(Rank(Sum({1<[FKDAT.autoCalendar.MonthsAgo] ={'1'},[ZBOTT_UMSATZ.VKORG] = {'UK01'}, VTWEG = {"BG","DP","EU","MO","MT","OD"}>}NETWR) <=3),
num(Sum({1<[FKDAT.autoCalendar.MonthsAgo] ={'1'},[ZBOTT_UMSATZ.VKORG] = {'UK01'}, VTWEG = {"BG","DP","EU","MO","MT","OD"}>}NETWR), ' £ #,##0 '))

& ', which is '

& IF(Rank(Sum({<[FKDAT.autoCalendar.MonthsAgo] ={1},[ZBOTT_UMSATZ.VKORG] = {'UK01'}, VTWEG = {"BG","DP","EU","MO","MT","OD"}>} NETWR) <=3, Sum(NETWR)
/ Sum({<[FKDAT.autoCalendar.MonthsAgo] ={1},[ZBOTT_UMSATZ.VKORG] = {'UK01'}, VTWEG = {"BG","DP","EU","MO","MT","OD"}>} NETWR) -1),' 0.0% ;')

&' of the whole months sales figure. '

Apologies, the first 3 lines of repetition, but I couldnt work out how to show all three customer names from one line of;

FirstSortedValue ([RGNAM], aggr(Rank(sum({<[FKDAT.autoCalendar.MonthsAgo] ={1},[ZBOTT_UMSATZ.VKORG] = {'UK01'}, VTWEG = {"BG","DP","EU","MO","MT","OD"}>}[NETWR]))=1,[RGNAM]))

But it all works fine, except the last;

& IF(Rank(Sum({<[FKDAT.autoCalendar.MonthsAgo] ={1},[ZBOTT_UMSATZ.VKORG] = {'UK01'}, VTWEG = {"BG","DP","EU","MO","MT","OD"}>} NETWR) <=3, Sum(NETWR)
/ Sum({<[FKDAT.autoCalendar.MonthsAgo] ={1},[ZBOTT_UMSATZ.VKORG] = {'UK01'}, VTWEG = {"BG","DP","EU","MO","MT","OD"}>} NETWR) -1),' 0.0% ;')

I was hoping to have the value of the top 3 customers (for last month, within a particular sales org (ZBOTT_UMSATZ_VKORG and channels VTWEG) by sales (NETWR) as a % against all the sales for that sales org/channel for last month.

Even if I clean that section (easier for you to read) up my result is still 0%

& IF(Rank(Sum(NETWR) <=3, Sum(NETWR)
/ Sum(NETWR) -1),' 0.0% ;')

I use % calc in many other titles, without issue, but never before used with 'RANK', is that my problem?

Sorry for the ugliness/untidiness of the expression!

If anyone can assist/offer advice or a solution, please.

Regards Daryn

Re: Dynamic title - issue with % calculation

Daryn — Thu, 22 Jun 2023 07:08:23 GMT

Any guru's have any ideas please? Thank you.

topic Re: Dynamic title - issue with % calculation in App Development

Dynamic title - issue with % calculation

Re: Dynamic title - issue with % calculation