find highest sales for each month in a time series bar chart

[arjunmadhusudan]

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.

[tresesco]

Like this:

Dual( FirstSortedValue(Company, -Aggr(Sum(Sales), [Month Year], Company))& ' : '& Max(Sales), Max(Sales))

[arjunmadhusudan]

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 ?

[thevingo]

bypass company in SET analysis

or

Use TOTAL keyword at company-level

[tresesco]

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, ....

[arjunmadhusudan]

Shows the highest from the whole list.

[tresesco]

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))

)

[arjunmadhusudan]

Results not matching with table value. There seems to be an issue with the expression logic.

[sunny_talwar]

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?

[arjunmadhusudan]

I checked with my data this seems to give me wrong values for sales.