Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

zagzebski
Contributor

Evaluating one month, summing on YTD

I have the following type data:

^DeptCustDeptCustomerCustomerPctYearMonthCommission
A|1A140%2012Jan100
A|4A450%2012Jan150
A|3A355%2012Jan200
A|2A260%2012Jan250
A|5A5100%2012Jan300
A|1A140%2012Feb400
A|2A260%2012Feb450
A|2A260%2012Feb500
A|3A365%2012Feb550
A|4A4100%2012Feb600

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.

3 Replies

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)

zagzebski
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!

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)

Community Browser