Set Analysis - Reporting Dates

[zagzebski]

I am an expression that is working well:

sum({<^CustProducer = {"=[Commission Producer CummPerc]<=$(vRetensionPct)"}>} Commission)

However I need to add a further requirement to the expression.  I have a field called ProdGLMonth and need to add extra filter ot the set analysis that basically does the following:

Where ProdGLMonth is between [Reporting Begin Month] and [Reporting End Month]

[#2]

If ProdGLMonth, [Reporting Begin Month], [Reporting End Month] are months created using QlikView or numbers try the following expression:

sum({<^CustProducer = {"=[Commission Producer CummPerc]<=$(vRetensionPct)"},ProdGLMonth = {">=[Reporting Begin Month]<=[Reporting End Month]"}>} Commission)

Regards, Karl

[#3 · Accepted solution]

Try this:

sum({<^CustProducer = {"=[Commission Producer CummPerc]<=$(vRetensionPct)"}, ProdGLMonth = {">=$(=num([Reporting Begin Month])) <=$(=num([Reporting End Month]))"}>} Commission)

assuming ProdGLMonth is a number presenting month.

Hope this helps

Herman

[#4]

Opps....I was late....

[#5]

Yeap, but you're right.  I forgot to add the dollar-sign expansion and the num function to my set analysis.

Regards, Karl

[zagzebski]

Thanks Karl!

I have difficulty with dates. I thought they all three fields were dates so I wouldn't have to use any num function.