Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

how to get average balance of month in qlikview

this Expression give me Current balance now how to get average balance of month for on Year

num(SUM( {<

[FinMonth] =

{"<=$(max(FinMonth))"},

[FinQuarterNum] =

{"<=$(max(FinQuarterNum))"},

[FinYear] =

{"<=$(vMaxYear)"}

>} (InFlowUnits-OutFlowsUnits)*nav_value) ,' #,##0 ')

Thanxs Regards,

Usman

8 Replies
MVP
MVP

Re: how to get average balance of month in qlikview

Try replacing sum() by avg().

Not applicable

Re: how to get average balance of month in qlikview

Hi,

Please try this

Num(Avg( {<

[FinMonth] ={"<=$(max(FinMonth))"},

[FinQuarterNum] ={"<=$(max(FinQuarterNum))"},

[FinYear] ={"<=$(vMaxYear)"}

>} (InFlowUnits-OutFlowsUnits)*nav_value) )

Hope it helps you.

Thanks and Regards,

S.Amuthabharathi

Not applicable

Re: how to get average balance of month in qlikview

Still Not Correct result . can u tell me how to get 12 month balance and then sum for example

1:100, sum(month)

2:100,sum(month)

3:300,sum(month)

4:100,sum(month)

5:100,sum(month)

6:200,sum(month)

7:100,sum(month)

8:300,sum(month)

9:400,sum(month)

10:400,sum(month)

11:100,sum(month)

12:200 sum(month)

then sum(all months)

sum(sum(month)) i want to do like this. how its possible?

MVP
MVP

Re: how to get average balance of month in qlikview

You can try something like:

Aggr(Sum({Your set expression} Expression), Month)    // to get monthly sums

I still doubt what you excatly need. Can you share a sample app?

v_iyyappan
Valued Contributor

Re: how to get average balance of month in qlikview

Hi

Try like this

Here FinalDate is ur datefield

= Num(Avg({<FinalDate = ">=$(=YearStart(max(FinalDate)))<=$(=max(FinalDate))">}(InFlowUnits-OutFlowsUnits)*nav_value),'#,##0.00')

Regards,

Not applicable

Re: how to get average balance of month in qlikview

tresesco

exactly i want to do

average balance month = sum(balance for month of every day )/noof transaction

this expression give me current Balance.

num(SUM( {<

[FinMonth] =

{"<=$(max(FinMonth))"},

[FinQuarterNum] =

{"<=$(max(FinQuarterNum))"},

[FinYear] =

{"<=$(vMaxYear)"}

>} (InFlowUnits-OutFlowsUnits)*nav_value) ,' #,##0 ')

how to create balancing for 30 day of selected month

Regards,

Usman

Not applicable

Re: how to get average balance of month in qlikview

exactly i want to do

average balance month = sum(balance for month of every day )/noof transaction

this expression give me current Balance.

num(SUM( {<

[FinMonth] =

{"<=$(max(FinMonth))"},

[FinQuarterNum] =

{"<=$(max(FinQuarterNum))"},

[FinYear] =

{"<=$(vMaxYear)"}

>} (InFlowUnits-OutFlowsUnits)*nav_value) ,' #,##0 ')

how to create balancing for 30 day of selected month

Regards,

Usman

v_iyyappan
Valued Contributor

Re: how to get average balance of month in qlikview

Hi,

Restrict the Month and Quarter selection. It will give bal of select Month. i want to know transaction fields

= Sum({<[FinMonth] =,[FinQuarterNum] =,FinalDate = ">=$(=MonthStart(max(FinalDate)))<=$(=max(FinalDate))">}(InFlowUnits-OutFlowsUnits)*nav_value) / no of transaction

Regards,