Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have Date as dimension in bar chart (presing latest 4 months date) and measure as sum(sales)..
Now i have to add another measure to find sum(sales) for last 3 months dynamically excluding current month.. for each date wise going back for last 3 months of Sum(sales)?
after that i have to calculate AVg sales --> current date(sales)/last 3 months sales
sample data below having dates from March, April, may, june month dates..
Could you please respond any one for this post..
giving more details...
If my date is may-18 --> then i have to get sum(sales) for FEb, March, APRil month later to find avg for last 3 months
if my dimesion date showing June-2 Means --> have to get sum(sales) for Mar,Apr, May Months
after finding avg for last 3 months (excluding current month dates)like this .. and i have to calculate like below
--> June2(sales) / 3 months (mar,Apr,May)avg sales
--> May15(sales)/ 3 months(Feb,mar,apr )vg sales
am using below Calculation to achieve this. but am getting each individual date sales instead last 3 month sales..
Sum({< [Report Date]={">=$(=MonthStart(Max([Report Date]), -3))<=$(=MonthEnd(Max([Report Date]), -1))"}>} Saleable )
table shown below:
I believe you need to use an aggregation.
Try:
AGGR( nodistinct Sum({1< [Report Date]={">=$(=Date(monthstart(=max([Report Date]),-3), 'MM/DD/YYYY'))<=$(=Date(monthend(=max([Report Date]),-1), 'MM/DD/YYYY'))"} Saleable ), [Report Date.autoCalendar.Month])
And then to get the daily avg for the AGGR.
Take the AGGR and add the following:
/ (date(monthend(max( [Report Date]),-1))-Date(monthstart(max( [Report Date]),-3)))
I would look into using The As-Of Table here
Hi Tm, Thanks for responding..
Not working this approch... getting null values ,, any alternative way?
Is the "Report Date" field an AutoCalendar field.
Yes, that is coming from auto calendar field and also i have tried with normal date converting to year- month format.
and 1 more thing i have tried below approach..
(Sum(Aggr(rangesum(above(sum(Saleable),1)), [Report Date.autoCalendar.YearMonth]))
+ Sum(Aggr(rangesum(above(sum(Saleable),2)), [Report Date.autoCalendar.YearMonth])) + Sum(Aggr(rangesum(above(sum(Saleable),3)), [Report Date.autoCalendar.YearMonth]))) Values summingup correctly but its displaying only first date of every month,, its not repeating till month end ( iam expecting to repeat because last 3 months its common for all dates in perticular month) screenshot below?
could you please suggest me how to drag these each month value to till month end ?
May be try this
Sum(Aggr(
RangeSum(Above(Sum(Saleable), 1, 3))
, [Report Date.autoCalendar.YearMonth]))
But isn't this doing it for just last 3 days? I thought you needed to do last 3 months? May be change 3 to 90?
Not working Sunny.. even i placed 90 days.. its accumulating all 90 days.. eventhough displaying only first date of every month.. and below code working fine when I have placed month as dimension.
but i should hide that month dimension in bar chart.. when i used condition show to hide this month dimension values also going off.. could you suggest me any way to achieve this?
= (Sum(Aggr(rangesum(above(sum(Saleable),1)), [Report Date.autoCalendar.Month] ))
+ Sum(Aggr(rangesum(above(sum(Saleable),2)),[Report Date.autoCalendar.Month] ))
+Sum(Aggr(rangesum(above(sum(Saleable),3)),[Report Date.autoCalendar.Month] )))