Last 3 Month Average Calculation based on Dimensional selection in the Table

[Lakshminarayanan_J]

Dear All,

Our Requirement is to build the Adhoc Report which need to display the Measure Based on Dimension selection.

Expression Logic Stock In Take:Current Month Closing Value/Last 3 Month Sales Value /3*30

Expression Used and Working Fine in Text Box :

num((Sum({<Date_SecSales_Monthend=,Month_No=>}Closing_Value)/
((Sum(Aggr(
RangeSum(Above(Sum({<Date_SecSales_Monthend=,Month_No=,Month=>}Sales_Value/3), 0, 3))
, Month)))))*30,'#,##0'))  

But In Adhoc Report Above Expression is not working and data are not displaying :

Dimensional In Adhoc Report are below :

BU

Region

Area

Territory

Distributor Code

Distributor

District

Brand

Kindly Help Us on Building the Expression in Table which need to Display Data based on Dimensional selection.

@tm_burgers  @sunny_talwar @eliasneif @kdmarkee @edwin @Kush

[edwin]

i would not solution this via expression.  i would build a bridge for this.  

assuming your measure ClosingValue is by day (FactDate),  (if just end of month, it will be simpler)
i would associate each date with the full 3 months of prior Sales.  assuming that you only have a single date (FactDate) for both closing and sales date.

with Dimension Month, your expression will just be sum({<DateType={'CURRENT'}>}ClosingValue)/Sum({<DateType={'3MONTHSBACK'}>}Sales) /3 * 30

since the closing monthend is already associated with the 3 months of sales data, you dont need any complicated set analysis condition for the sales. this is how you build that bridge:

Calendar:
load date(today()-RowNo()) as Date;
load 1 as num AutoGenerate(365);

Fact:
load Date as FactDate, 'CLOSING' as Fact, floor(Rand()*10000)/100 as CloseValue Resident Calendar;

Concatenate (Fact)
load Date as FactDate, 'SALES' as Fact, floor(Rand()*10000)/100 as SalesAmount Resident Calendar;

NoConcatenate
tmpBridge:
load Date Resident Calendar;

inner join (tmpBridge) 
load Date as FactDate Resident Calendar;

NoConcatenate
Bridge:
load Date, FactDate, '3MONTHSBACK' as DateType Resident tmpBridge
where MonthStart(FactDate)>=AddMonths(MonthStart(Date),-3) 
and MonthStart(FactDate)<=AddMonths(MonthStart(Date),-1);

Concatenate (Bridge)
load Date, Date as FactDate, 'CURRENT' as DateType Resident Calendar;

drop table tmpBridge;

in the attached pic, the first chart is the straight totals per month.  the second chart is the total close per month and the corresponding prior 3 months of sales

[edwin]

BTW, shouldnt you be dividing by count(distinct {<DateType={'3MONTHSBACK'}>}FactDate) instead of 30?

[Lakshminarayanan_J]

Hi Edwin,

Thanks for the response , Need a clarification on a Bridge,

• We already have the sales_Value and Closing_Value in the table , Kindly let me know why we are creating the both the field in  Bridge table.
• We need to display data based on Dimensional data like Area,Region, Territory not on month wise.

Kindly help us in clarifying on this ,

Thank You,

[MayilVahanan]

HI @Lakshminarayanan_J 

Can you provide the sample data to analysis further.

[edwin]

The purpose of the bridge is to create a relationship between the date you selected and the dates applicable to your expression:
DateType CURRENT - means one to one.  select Oct 5, 2020 and it is related to all Oct 5, 2020 rows in your fact table

3MONSBACK - means one selection will relate to all the rows in your fact with dates representing the whole month 3 months back.

the bridge does not contain any measure.  you may have confused the part of the script where i have these measures - it is there just to simulate your fact table.