Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to create a KPI Panel but I need some help to do so. In a table similar to the one below:
Sales Nº | Month | Volume Sold | Financial profit/loss |
1 | Jan | 500 | $ 2,000.00 |
2 | Jan | 520 | $ 1,200.00 |
3 | Jan | 530 | $ 2,200.00 |
4 | Feb | 490 | $ 8,000.00 |
5 | Feb | 470 | $ 3,200.00 |
6 | Feb | 580 | $ 3,100.00 |
7 | Feb | 620 | $ 1,600.00 |
8 | Feb | 200 | - $ 1,000.00 |
9 | Mar | 150 | - $ 500.00 |
10 | Mar | 700 | $ 3,000.00 |
I'd like a way to express the following data (not only the number but also the corresponding Month)
I already know how to use, for exemple, Max() function to obtain the most profitable sale but I don't know how to express not only the number but the corresponding row of the number (the correspondig sale nº). I also know how to use the count() function for the whole table but not for each month and only then getting the max() of the previous calculation.
i'd like some help with that, please. Thank you in advance
Hello, I send you the code, with that what you require is fulfilled.
1.- Most sales made in a month
Count({< Month = {"=Aggr(Rank(MaxString([Financial profit/loss]), Month), Month) = 1"} >} [Sales Nº]) & ' Sales in ' & MaxString({< Month = {"=Aggr(Rank(MaxString([Financial profit/loss]), Month), Month) = 1"} >} Month)
2.- Most profitable sale
=Num(Max([Financial profit/loss]), '$#,##0.00') & '; ' &
If(MaxString({< [Financial profit/loss] = {"=Max(TOTAL [Financial profit/loss])"} >} [Sales Nº]) = 1, '1st',
If(MaxString({< [Financial profit/loss] = {"=Max(TOTAL [Financial profit/loss])"} >} [Sales Nº]) = 2, '2nd',
If(MaxString({< [Financial profit/loss] = {"=Max(TOTAL [Financial profit/loss])"} >} [Sales Nº]) = 3, '3rd',
MaxString({< [Financial profit/loss] = {"=Max(TOTAL [Financial profit/loss])"} >} [Sales Nº]) & 'th Sale')))
3.- Greates financial loss
=Num(Min([Financial profit/loss]), '$#,##0.00') & '; ' &
If(MinString({< [Financial profit/loss] = {"=Min(TOTAL [Financial profit/loss])"} >} [Sales Nº]) = 1, '1st',
If(MinString({< [Financial profit/loss] = {"=Min(TOTAL [Financial profit/loss])"} >} [Sales Nº]) = 2, '2nd',
If(MinString({< [Financial profit/loss] = {"=Min(TOTAL [Financial profit/loss])"} >} [Sales Nº]) = 3, '3rd',
If(Rank([Financial profit/loss]) = 1,
'$' & Replace(Num(Min([Financial profit/loss]), '#,##0.00'), ',', '.') & '; ' & [Sales Nº] & 'st sale',
If(Rank([Financial profit/loss]) = 2,
'$' & Replace(Num(Min([Financial profit/loss]), '#,##0.00'), ',', '.') & '; ' & [Sales Nº] & 'nd sale',
If(Rank([Financial profit/loss]) = 3,
'$' & Replace(Num(Min([Financial profit/loss]), '#,##0.00'), ',', '.') & '; ' & [Sales Nº] & 'rd sale',
'$' & Replace(Num(Min([Financial profit/loss]), '#,##0.00'), ',', '.') & '; ' & [Sales Nº] & 'th sale'
))))))
4.- Most profitable month
=Num(Max(TOTAL Aggr(Sum([Financial profit/loss]), Month)), '$#,##0.00') & '; ' &
MaxString({< [Financial profit/loss] = {"=Max(TOTAL Aggr(Sum([Financial profit/loss]), Month))"} >} Month)
5.- Lowest profitable month
=Num(Min(TOTAL Aggr(Sum([Financial profit/loss]), Month)), '$#,##0.00') & '; ' &
MaxString({< [Financial profit/loss] = {"=Min(TOTAL Aggr(Sum([Financial profit/loss]), Month))"} >} Month)
Here the test table in script:
Table:
LOAD * INLINE [
Sales Nº, Month, Volume Sold, Financial profit/loss
1, Jan, 500,2000.00
2, Jan, 520,1200.00
3, Jan, 530,2200.00
4, Feb, 490,8000.00
5, Feb, 470,3200.00
6, Feb, 580,3100.00
7, Feb, 620,1600.00
8, Feb, 200,-1000.00
9, Mar, 150,-500.00
10, Mar, 700,3000.00
];
Results:
Regarts.
Just use FirstSortedValue()
Load * inline [
SalesNo,MonthNm,VolumeSold,profit_loss
1,Jan,500,2000.00
2,Jan,520,1200.00
3,Jan,530,2200.00
4,Feb,490,8000.00
5,Feb,470,3200.00
6,Feb,580,3100.00
7,Feb,620,1600.00
8,Feb,200,-1000.00
9,Mar,150,-500.00
10,Mar,700,3000.00
];
exit Script;
Hello, I send you the code, with that what you require is fulfilled.
1.- Most sales made in a month
Count({< Month = {"=Aggr(Rank(MaxString([Financial profit/loss]), Month), Month) = 1"} >} [Sales Nº]) & ' Sales in ' & MaxString({< Month = {"=Aggr(Rank(MaxString([Financial profit/loss]), Month), Month) = 1"} >} Month)
2.- Most profitable sale
=Num(Max([Financial profit/loss]), '$#,##0.00') & '; ' &
If(MaxString({< [Financial profit/loss] = {"=Max(TOTAL [Financial profit/loss])"} >} [Sales Nº]) = 1, '1st',
If(MaxString({< [Financial profit/loss] = {"=Max(TOTAL [Financial profit/loss])"} >} [Sales Nº]) = 2, '2nd',
If(MaxString({< [Financial profit/loss] = {"=Max(TOTAL [Financial profit/loss])"} >} [Sales Nº]) = 3, '3rd',
MaxString({< [Financial profit/loss] = {"=Max(TOTAL [Financial profit/loss])"} >} [Sales Nº]) & 'th Sale')))
3.- Greates financial loss
=Num(Min([Financial profit/loss]), '$#,##0.00') & '; ' &
If(MinString({< [Financial profit/loss] = {"=Min(TOTAL [Financial profit/loss])"} >} [Sales Nº]) = 1, '1st',
If(MinString({< [Financial profit/loss] = {"=Min(TOTAL [Financial profit/loss])"} >} [Sales Nº]) = 2, '2nd',
If(MinString({< [Financial profit/loss] = {"=Min(TOTAL [Financial profit/loss])"} >} [Sales Nº]) = 3, '3rd',
If(Rank([Financial profit/loss]) = 1,
'$' & Replace(Num(Min([Financial profit/loss]), '#,##0.00'), ',', '.') & '; ' & [Sales Nº] & 'st sale',
If(Rank([Financial profit/loss]) = 2,
'$' & Replace(Num(Min([Financial profit/loss]), '#,##0.00'), ',', '.') & '; ' & [Sales Nº] & 'nd sale',
If(Rank([Financial profit/loss]) = 3,
'$' & Replace(Num(Min([Financial profit/loss]), '#,##0.00'), ',', '.') & '; ' & [Sales Nº] & 'rd sale',
'$' & Replace(Num(Min([Financial profit/loss]), '#,##0.00'), ',', '.') & '; ' & [Sales Nº] & 'th sale'
))))))
4.- Most profitable month
=Num(Max(TOTAL Aggr(Sum([Financial profit/loss]), Month)), '$#,##0.00') & '; ' &
MaxString({< [Financial profit/loss] = {"=Max(TOTAL Aggr(Sum([Financial profit/loss]), Month))"} >} Month)
5.- Lowest profitable month
=Num(Min(TOTAL Aggr(Sum([Financial profit/loss]), Month)), '$#,##0.00') & '; ' &
MaxString({< [Financial profit/loss] = {"=Min(TOTAL Aggr(Sum([Financial profit/loss]), Month))"} >} Month)
Here the test table in script:
Table:
LOAD * INLINE [
Sales Nº, Month, Volume Sold, Financial profit/loss
1, Jan, 500,2000.00
2, Jan, 520,1200.00
3, Jan, 530,2200.00
4, Feb, 490,8000.00
5, Feb, 470,3200.00
6, Feb, 580,3100.00
7, Feb, 620,1600.00
8, Feb, 200,-1000.00
9, Mar, 150,-500.00
10, Mar, 700,3000.00
];
Results:
Regarts.
Just use FirstSortedValue()
Load * inline [
SalesNo,MonthNm,VolumeSold,profit_loss
1,Jan,500,2000.00
2,Jan,520,1200.00
3,Jan,530,2200.00
4,Feb,490,8000.00
5,Feb,470,3200.00
6,Feb,580,3100.00
7,Feb,620,1600.00
8,Feb,200,-1000.00
9,Mar,150,-500.00
10,Mar,700,3000.00
];
exit Script;