# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Contributor

## Evaluating one month, summing on YTD

I have the following type data:

 ^DeptCust Dept Customer CustomerPct Year Month Commission A|1 A 1 40% 2012 Jan 100 A|4 A 4 50% 2012 Jan 150 A|3 A 3 55% 2012 Jan 200 A|2 A 2 60% 2012 Jan 250 A|5 A 5 100% 2012 Jan 300 A|1 A 1 40% 2012 Feb 400 A|2 A 2 60% 2012 Feb 450 A|2 A 2 60% 2012 Feb 500 A|3 A 3 65% 2012 Feb 550 A|4 A 4 100% 2012 Feb 600

I need an object where a year and month are selected and  a variable for the CustomerPct. So (use data above) if a user selects Feb and 2012 and a CustomerPct of 65%, then the object returns all data from February that has a % of 65% or less. This expression works fine:

sum({<^DeptCust = {"=[CustomerPct]<=\$(vRetensionPct)"} >}Commission)

and  would return all blue lines from Feb 2012.

However I need it to take all months = or less than the month selected FOR the combinations that are valid for February (A|1,A|2, and A|3).

The result would look like:

Dept          Commission     Distinct Customer Count

A               2450                              3

Looking for Commission to be YTD and how to get a distinct customer count.

Tags (2)
3 Replies
MVP

## Re: Evaluating one month, summing on YTD

Have Dept has dimesnion

Two expression:

1). Sum({<^DeptCust = {"=[CustomerPct]<=\$(vRetensionPct)"} >}Commission)

2). Count({<^DeptCust = {"=[CustomerPct]<=\$(vRetensionPct)"} >} DISTINCT Customer)

Contributor

## Re: Evaluating one month, summing on YTD

Sum({<^DeptCust = {"=[CustomerPct]<=\$(vRetensionPct)"} >}Commission)

This formula doesn't work because if I select February it gives me ONLY February commission. I need February plus all previous months (ie YTD). So choosing Feb 2012 and the variable percent establishes what customer/dept combinations you want but then I need it to be all YTD commissions of that combination...essentially all the lines in blue in my data set and the object shows the result (above).

Thanks for helping!

MVP

## Re: Evaluating one month, summing on YTD

Do you have date field?

Make use of it.

Sum({<^DeptCust = {"=[CustomerPct]<=\$(vRetensionPct)"}, DateField={">=\$(=YearStart(Max(DateField)))<=\$(=Max(DateField))"}, Year=, Month= >}Commission)