Aggr() doesn't give correct minimum value when i have more than one dimension in straight table

[#1]

Hello All,

I am trying to use aggr() to get minimum sale value from a period of 6 months for a Car model.

I want to keep the period selection as user defined. Hence, trying to use the variable (monthslab).

In below expression i am trying to define the time period using set analysis and then grouping it by Month and Sales model using aggr().

This gives desired output.

=min(aggr(Count({<Month_SDate = {'>=$(=monthstart(AddMonths(max(TOTAL SDate),-$(monthslab)))) <=$(=monthend(AddMonths(max(TOTAL SDate),-1)))'}>}[retail.S No]),Month_SDate,SalesModel))

if i try to have Variant and Color dimensions in the table and add these columns in my expression, it skips the zero value and gives the next one.

min(aggr(Count({<Month_SDate = {'>=$(=monthstart(AddMonths(max(TOTAL SDate),-$(monthslab)))) <=$(=monthend(AddMonths(max(TOTAL SDate),-1)))'}>}[retail.S No]),Month_SDate,SalesModel,Sales.Variant,Sales.Color))

Please help!!!

[sunny_talwar]

I guess only the Min changes? what about the first 3 expressions? They would always stay the same regardless of 3 or 6 or 9 or 12 or 24 months?

[#3]

Yes Sunny... Only the Min should change. Once this accomplished i will be adding Max and Avg as well on the same lines.

[rubenmarin]

Hi Harish, you can use something similar to the way you get the TOTAL column, in case of Min:

=RangeMin(Column(1), Column(2), Column(3))

[sunny_talwar]

Here try this:

=Min(Aggr(Count({<SDate = {"$(='>=' & Date(MonthStart(Max(SDate),-$(monthslab)), 'DD-MM-YYYY') & '<=' & Date(MonthEnd(Max(SDate), -1), 'DD-MM-YYYY'))"}>}[retail.S No]), Month_SDate, SalesModel, retail.Variant, retail.Colour))