Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following data:
Company | Month Year | Sales |
a | Jan-17 | 10 |
a | Feb-17 | 20 |
a | Mar-17 | 11 |
a | Apr-17 | 13 |
b | Jan-17 | 40 |
b | Feb-17 | 45 |
b | Mar-17 | 2 |
b | Apr-17 | 4 |
c | Jan-17 | 55 |
c | Feb-17 | 3 |
c | Mar-17 | 40 |
c | Apr-17 | 1 |
I need a times series bar chart which has X axis = Month Year and Y axis = Highest sale for that time period group by company
For example for Jan-17 I should get sale = 55 which for company c.
Like this:
Dual( FirstSortedValue(Company, -Aggr(Sum(Sales), [Month Year], Company))& ' : '& Max(Sales), Max(Sales))
I need also have an exclude filter for company. Company selected in the filter should not be part of calculation.
How can we do this ?
bypass company in SET analysis
or
Use TOTAL keyword at company-level
You may try with:
Dual(FirstSortedValue({<Company=e(Company)>}Company, -Aggr(Sum({<Company=e(Company)>}Sales), [Month Year], Company))& ' : '& Max({<Company=e(Company)>}Sales), Max({<Company=e(Company)>}Sales))
With this, when noting is selected from exclude filter, all companies would be excluded, hence the chart would go blank. If you want to put a condition like when at least one value from the exclude filter is selected then only the exclusion should work, you might try with additional conditional check like: If(GetSelectedCount(Company)>0, ....
Shows the highest from the whole list.
Try like:
If( GetSelectedCount(Company)>0,
Dual(FirstSortedValue({<Company=e(Company)>}Company, -Aggr(Sum({<Company=e(Company)>}Sales), [Month Year], Company))& ' : '& Max({<Company=e(Company)>}Sales), Max({<Company=e(Company)>}Sales)),
Dual(FirstSortedValue(Company, -Aggr(Sum(Sales), [Month Year], Company))& ' : '& Max(Sales), Max(Sales))
)
Results not matching with table value. There seems to be an issue with the expression logic.
Results not matching with table value.
Which table are you talking about? Can you elaborate with an example as to why you think tresesco's expression isn't working?
I checked with my data this seems to give me wrong values for sales.