topic Re: Dynamic Year to Date Calculation in QlikView

Dynamic Year to Date Calculation

Sat, 17 May 2014 17:14:48 GMT

Hello All,

I'm hoping to get some help with a set expression that dynamically calculates a YTD sum based on selections. Attached is the qvw.

I'm successful on calculating YTD based on a selection of "Year" and 'Month" using the following.

sum({1<CalDate= {'>=$(=YearStart(Max(CalDate)))<=$(=Max(CalDate))'}>}NetCount)

Because I'm using a "1" I'm unable now to select a state and get correct results.

If I take out the "1" I get cumulative sum of only the "Year" and "Month" (MTD) that is selected.

I'm sure there is a simple solution here.

Thanks in advance for any help.

Re: Dynamic Year to Date Calculation

its_anandrjs — Sat, 17 May 2014 17:37:51 GMT

Hi,

I believe your expression work fines that is

sum({1<CalDate= {'>=$(=YearStart(Max(CalDate)))<=$(=Max(CalDate))'}>}NetCount)

and why you want to remove the 1 from expression.If you select Year then month it will calculate the YTD calculation.

Re: Dynamic Year to Date Calculation

Sat, 17 May 2014 17:57:34 GMT

Yes...that is true but when I select a "State" (list box) I would expect to see a YTD result for the state selected. With the 1 it does not consider the selection of state.

Re: Dynamic Year to Date Calculation

Sat, 17 May 2014 20:38:07 GMT

Why not:

sum({<Year=, Month=,CalDate= {'>=$(=YearStart(Max(CalDate)))<=$(=Max(CalDate))'}>}NetCount)

Re: Dynamic Year to Date Calculation

Sun, 18 May 2014 18:21:57 GMT

Thanks Dariusz! That work perfectly.