Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
Not sure I understand when you say it skips the zero value. Would you be able to share a sample?
Do you have a sample app?
!
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
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])))
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.
Are these going to be done using a variable to the same expression that you have? or you plan to create new expressions?
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))
