Average Issue

[Anonymous]

Hi All,

I have an issue on Averages when am calculating averages for last 4 months , if there is a data in last 4 months it is dividing by 4 and giving correct result but if i don't have data in 2 months out of 4 ,so the requirement says it should divide by 2 not 4

How can we achieve this ., can anybody help me

[lakshmikandh]

Sum(Amount) / Sum(Aggr(if(Match(Sum(Amount),0),1,0),Month)) Try this

[jonathandienst]

Is that not how the averages in QV work already? However, if for some reason (for example, the "no data" is zeroes rather than nulls or missing values) you can calculate the average by dividing the sum by the count (and filter the count to give the correct number of months).

[Anonymous]

if there is no data , it represents NDR (No data received) like

IF(WILDMATCH(MINSTRING({$<%dim_Data_Type={'LIQUIDITY'},%dim_Category={"INITIAL MARGIN (RESERVES)"},%dim_Metric_Name={"RESERVES"},%dim_Business_Month_End_Date={"$(=Date(Date#(vCal_MonthEndSelected,'DD MMM YY'),'DD MMM YY'))"}>}%dim_Metric_Value),'*N/A*','*NDR*')=0,

PICK(MATCH(ONLY({$<%dim_Data_Type={'LIQUIDITY'},%dim_Category={"INITIAL MARGIN (RESERVES)"},%dim_Metric_Name={"RESERVES"},%dim_Business_Month_End_Date={"$(=Date(Date#(vCal_MonthEndSelected,'DD MMM YY'),'DD MMM YY'))"}>}[Liquidity Currency]),'GBP','EUR','USD'),'£ ',' € ','$ ')&

Sum({$<%dim_Data_Type={'LIQUIDITY'},%dim_Category={"INITIAL MARGIN (RESERVES)"},%dim_Metric_Name={"RESERVES"},%dim_Business_Month_End_Date={"$(=Date(Date#(vCal_MonthEndSelected,'DD MMM YY'),'DD MMM YY'))"}>}%dim_Metric_Value)*1000000000,

IF(WILDMATCH(MAXSTRING({$<%dim_Data_Type={'LIQUIDITY'},%dim_Category={"INITIAL MARGIN (RESERVES)"},%dim_Metric_Name={"RESERVES"},%dim_Business_Month_End_Date={"$(=Date(Date#(vCal_MonthEndSelected,'DD MMM YY'),'DD MMM YY'))"}>}%dim_Metric_Value),'*NDR*')>0, 'NDR','N/A'))

-----------------------------------------------------------------------

Average --

Sum({$<%dim_Data_Type={'LIQUIDITY'},%dim_Category={"INITIAL MARGIN (RESERVES)"},%dim_Sub_Category={"INITIAL MARGIN (RESERVES)"},%dim_Metric_Name={"RESERVES"},%dim_Business_Month_End_Date={"<=$(=Date(Date#(vCal_MonthEndSelected,'DD MMM YY'),'DD MMM YY')) >=$(=Date(Addmonths(Date#(vCal_MonthEndSelected,'DD MMM YY'),(vChartOption_BUL_TAB01_NoOfPeriod-1)*(IF(vChartOption_BUL_TAB01_PeriodType='[Business Qtr End Date]',-3,-1))),'DD MMM YY'))"}>}%dim_Metric_Value)*1000000000

    /  (vChartOption_BUL_TAB01_NoOfPeriod - COUNT(

IF(AGGR(ONLY({$<%dim_Data_Type={"LIQUIDITY"}>} %dim_Metric_Value),[Business Month End Date])='NDR',

[Business Month End Date])))

not working correctly ,for this exp am getting correct result when we have data in all months