Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Sum corresponding field in script

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

L

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Sum corresponding field in script

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;


talk is cheap, supply exceeds demand
4 Replies
Not applicable

Re: Sum corresponding field in script

Hello,

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

Best.

Not applicable

Re: Sum corresponding field in script

It gave me an 'Invalid Expression' error

I changed slightly to

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

MVP & Luminary
MVP & Luminary

Re: Sum corresponding field in script

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;


talk is cheap, supply exceeds demand
Not applicable

Re: Sum corresponding field in script

Perfect!

Did exactly what I needed.

Thank you so much for your Help Gysbert