Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

priyan_pa
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

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

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

,FiscalMonthYear))

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

2 Replies

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

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

,FiscalMonthYear))

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

priyan_pa
New Contributor III

Re: Sum of Rows Equivalent in a single value

Many thanks for prompt response.

Community Browser