Average Issue

Anonymous — Thu, 06 Oct 2016 10:16:27 GMT

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

Re: Average Issue

lakshmikandh — Thu, 06 Oct 2016 10:29:10 GMT

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

Re: Average Issue

jonathandienst — Thu, 06 Oct 2016 10:35:22 GMT

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).

Re: Average Issue

Anonymous — Thu, 06 Oct 2016 11:11:14 GMT

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

topic Re: Average Issue in QlikView

Average Issue

Re: Average Issue

Re: Average Issue

Re: Average Issue