topic Re: Sum from January to selected month in QlikView

Sum from January to selected month

Mon, 06 Aug 2012 20:25:31 GMT

Hi,

I have data as shown below:

Year	Month	Sales
2000	January	10
2000	February	10
2000	March	10
2000	April	10
2000	May	10
2000	June	10
2000	July	10
2000	August	10
2000	September	10
2000	October	10
2000	November	10
2000	December	10
2001	January	20
2001	February	20
2001	March	20
2001	April	20
2001	May	20
2001	June	20
2001	July	20
2001	August	20
2001	September	20
2001	October	20
2001	November	20
2001	December	20

I need to do Sum of Sales between January of Selected year till Selected Month of that Year .for eg In above scenario if I select Year =2000(listbox) and Month=March(listbox) then expression should show sum= 30.

Re: Sum from January to selected month

MayilVahanan — Mon, 06 Aug 2012 20:34:37 GMT

Do you have date field?

If so, use like this,

=Sum({<DateField = {'>=$(=MonthStart(Min({1}DateField))) <= $(=MonthEnd(DateField))'}>}Sales)

Hope it helps

Re: Sum from January to selected month

Tue, 07 Aug 2012 03:03:09 GMT

Hi Mayil,

Thanks for response. Its giving me the result as 0.

As we are using Year and Month as List box in Dashboard ,the Date Field is present in the script but not in Dashboard So user can only select Year and Month not Date Field.

Re: Sum from January to selected month

Tue, 07 Aug 2012 03:03:37 GMT

Hi Mayil,

Thanks for response. Its giving me the result as 0.

As we are using Year and Month as List box in Dashboard ,the Date Field is present in the script but not in Dashboard So user can only select Year and Month not Date Field.

Re: Sum from January to selected month

MayilVahanan — Sat, 11 Aug 2012 20:41:10 GMT

Can you post a sample file?

Re: Sum from January to selected month

Anonymous — Sat, 11 Aug 2012 23:30:31 GMT

Try using function InYearToDate(). Probably like this, or close to it (assuming Month has numeric value, and Date is sales date):

sum({1} if(InYearToDate(Date, monthend(makedate(Year,Month)), 0), Sales))

Regards,

Michael