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]

Not sure I understand when you say it skips the zero value. Would you be able to share a sample?

[Jeff_Koch]

Do you have a sample app?

[#4]

!

[#5]

Hi Sunny and Jeff,

I have attached the sample qv file and sample base files which i am using.

I have left a note in qv with examples of what i am trying to solve.

Please have a look

[#6]

Hi Sunny and Jeff,

I have attached the sample qv file and sample base files which i am using.

I have left a note in qv with examples of what i am trying to solve.

Please have a look

[sunny_talwar]

May be use RangeMin()

=RangeMin(

Count(if(monthname(SDate) = monthname(monthname(monthname(max(TOTAL MonthName(SDate))-1)-1)-1),[retail.S No])),

Count(if(monthname(SDate) = monthname(monthname(max(TOTAL MonthName(SDate))-1)-1),[retail.S No])),

Count(if(monthname(SDate) = monthname(max(TOTAL MonthName(SDate))-1),[retail.S No])))

[#8]

My requirement was to keep the month period dynamic. I wanted the user to decide if he wanted to use 3 or 6 or 9 or 12 or 24 months and the Avg, Min and Max functions should change for the selected time period.

[sunny_talwar]

Are these going to be done using a variable to the same expression that you have? or you plan to create new expressions?

[#10]

I wanted to do it in the same expression.

I have used the variable "monthslab" in my expression.

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