topic Re: Sum of Rows Equivalent in a single value in QlikView

Sum of Rows Equivalent in a single value

Anonymous — Tue, 23 Aug 2016 13:58:28 GMT

Hi,

I have a situation where I calculate departed over Head Count on each month. Simple Formula with Sum(DepartedCount)/Sum(HeadCount) will provide the required calculation. I need to accumulate these values for each month over a fiscal year to date. ie. If the user select Jan as the month, values from July to Jan should be accumulated similar to sum of rows in an expression in a straight table. The situation is complicated due to calculating months upto fiscal year to date using set analysis by ignoring monthly and yearly selections.

See sample attached.

The expected outcome is 13.18% in a text box, for the selections:

CalendarYearDisplay = 2016

Month = Jan

Appreciate any assistance.

Re: Sum of Rows Equivalent in a single value

sunny_talwar — Tue, 23 Aug 2016 15:19:49 GMT

Try this:

=NUM(

Sum({$<Month=,CalendarYearDisplay=, FiscalYear={"$(=max(FiscalYear))"},FiscalMonthNumber={"<=$(=max(FiscalMonthNumber))"}>}Aggr(

Sum({$<Month=,CalendarYearDisplay=, FiscalYear={"$(=max(FiscalYear))"},FiscalMonthNumber={"<=$(=max(FiscalMonthNumber))"}>}DepartedCount)/

Sum({$<Month=,CalendarYearDisplay=, FiscalYear={"$(=max(FiscalYear))"},FiscalMonthNumber={"<=$(=max(FiscalMonthNumber))"}>}HeadCount)

,FiscalMonthYear))

,'#,##0.##%')

Re: Sum of Rows Equivalent in a single value

Anonymous — Tue, 23 Aug 2016 20:29:18 GMT

Many thanks for prompt response.