# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

New Contributor III

## Sum of Rows Equivalent in a single value

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.

1 Solution

Accepted Solutions
MVP

## Re: Sum of Rows Equivalent in a single value

Try this:

=NUM(

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

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

,FiscalMonthYear))

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

2 Replies
MVP

## Re: Sum of Rows Equivalent in a single value

Try this:

=NUM(

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

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