topic Sum corresponding field in script in QlikView

Sum corresponding field in script

Fri, 25 Jan 2013 16:12:20 GMT

Hi Guys,

I have a field in my master calendar for MonthYear (MMM-YY)

I also have a field in there to Flag certain days (contains 1 or 0)

The MasterCalendar data holds a record for each day.

I want to produce another table which will sum all of the flags that contain 1 for each MonthYear.

I tried something like this:

SumFlags:

LOAD MonthYear,

SUM(Flags)

RESIDENT MasterCalendar;

But Qlikview gave me an error.

I would like it end up with a table as below:

MonthYear FlagTotal

Jan-12 15

Feb-12 18

Mar-12 20

Can anyone help with getting the right script for this?

Thanks

Fri, 25 Jan 2013 16:25:08 GMT

Hello,

Try Sum(if(Flags=1,'1','0'))

Best.

Fri, 25 Jan 2013 16:31:08 GMT

It gave me an 'Invalid Expression' error

I changed slightly to

Sum(if(Flags=1,'1','0')) AS FlagCount

Gysbert_Wassenaar — Fri, 25 Jan 2013 16:50:16 GMT

If you use an aggregation function like sum then all the fields not used in the aggregation functions need to be included in a group by clause.

SumFlags:

LOAD MonthYear,

SUM(Flags)

RESIDENT MasterCalendar

group by MonthYear;

Fri, 25 Jan 2013 16:55:03 GMT

Perfect!

Did exactly what I needed.

Thank you so much for your Help Gysbert