topic Re: Last 3 Month Average Calculation based on Dimensional selection in the Table in QlikView

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

Lakshminarayanan_J — Fri, 15 Nov 2024 23:37:42 GMT

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 :

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

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

edwin — Mon, 16 Nov 2020 21:12:18 GMT

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

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

edwin — Mon, 16 Nov 2020 21:26:53 GMT

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

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

Lakshminarayanan_J — Tue, 17 Nov 2020 06:11:56 GMT

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,

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

MayilVahanan — Tue, 17 Nov 2020 06:25:47 GMT

HI @Lakshminarayanan_J

Can you provide the sample data to analysis further.

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

edwin — Thu, 19 Nov 2020 13:31:57 GMT

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.