topic Re: Sum in set analysis in QlikView

Sum in set analysis

Wed, 10 Apr 2013 15:09:40 GMT

Hi. I am trying to sum a field called "losses" using set analysis. However, I am trying to due it based on a date range.

I set up two variables. One called "vMinDatePY" and "vMaxDatePY". These take the current date range chosen and get the min and max of that date.

So if you choose the year 2013, the min date would be 1/1/2012 and max date variable would be 12/31/2012.

Here is my current equation...

Sum({<AccounttheDate = {">=$(vMinDatePY)", "<=$(vMaxDatePY)"}, Year=, Month= >} Losses)

It keeps coming back as $0. How do I fix this?

Thanks!

Re: Sum in set analysis

alexandros17 — Wed, 10 Apr 2013 15:30:00 GMT

try this

Sum({<AccounttheDate = {'>=$(vMinDatePY)<=$(vMaxDatePY)'}, Year=, Month= >} Losses)

Re: Sum in set analysis

Wed, 10 Apr 2013 15:39:58 GMT

Still brings back $0.

What's interesting is its bringing back 0, not null. So its working, technically. I put in display boxes with the two variables, and they do bring back the correct date range.

Could this be a different issue and not the equation?

Re: Sum in set analysis

alexandros17 — Wed, 10 Apr 2013 15:53:43 GMT

If you are sure variables are correct try the following:

in the script convert the field AccounttheDate to Num(AccounttheDate)

do the same thing to the two variables

this transform dates in equivalent numbers, in this way you can verify if everithing is working.

if it works then the issue is date format that you can solve with date(nnnnnn, 'dd/MM/yyyy')

to understand better the problem try

Sum({<AccounttheDate = {'>=20130101<=20130401'}, Year=, Month= >} Losses) it must work.

Let me know