If or where in a chart expression

[#1]

If([Pledge Campaign Year]<[Gift Campaign Year], sum([Gift Amount]), Null())

Trying to use this code in a chart expression to sum only the gifts that have a campaign year < pledge campaign year but it isn't working.

[swuehl]

Try

sum(If([Pledge Campaign Year]<[Gift Campaign Year], Gift Amount]))

[sunny_talwar]

You might also be able to use set analysis if you are able to figure out a dimension which uniquely identifies each and every row in your script or create one.

LOAD RowNo() as Key,

          [Pledge Campaign Year],

          [Gift Campaign Year],

          [Gift Amount]

FROM ...

and then use this:

Sum({<Key = {"=[Pledge Campaign Year] < [Gift Campaign Year]"}>} [Gift Amount])

[swuehl]

Sunny, what would be the benefit in this case using set analysis, if you apply the advanced search upon a hypercube built with a dimension on row granularity?

I don't think it's advantageous to apply set analysis just to use it.

Just my 0.02 €

[sunny_talwar]

How about something like this:

LOAD AutoNumber([Pledge Campaign Year]&[Gift Campaign Year]) as Key,

          [Pledge Campaign Year],

          [Gift Campaign Year],

          [Gift Amount]

FROM ...

and then use this:

Sum({<Key = {"=[Pledge Campaign Year] < [Gift Campaign Year]"}>} [Gift Amount])

Would this be a better solution you think?

[swuehl]

It will depend on the possible combinations of the year field.

Probably better than doing a per row comparison, right.

What about

LOAD If([Pledge Campaign Year] < [Gift Campaign Year],1,0) as Flag,

          [Pledge Campaign Year],

          [Gift Campaign Year],

          [Gift Amount]

FROM ...

and then use this:

Sum({<Flag = {1} >} [Gift Amount])

or

Sum(Flag*[Gift Amount])

My comment was more about using set analysis with care, we are often tempted to use it as often as possible...

Regards,

Stefan

[sunny_talwar]

No, I think you are right. I have never thought about it in such a way. Thanks for sending me in that direction . I think Flag option makes much more sense

[#8]

The problem that I am having is that I have a table linked to an alias of itself.

GiftTable

GiftTable2

where gifttable.pledgekey = gifttable2.giftkey

  and gifttable.campaignyr > gifttable2.campaignyr.

So when I try this join it doesn't recognize the field.

Field not found - <GIFTTable1 Campaign Year>

LEFT JOIN (GiftTable) LOAD

  [Gifttable2 PledgeKey] AS [Gift Pledge Key],

  [GiftTable2 Campaign Year] as [Gifttable2 Campaign Year],

  [Gifttable2 GiftKey]

RESIDENT Gifttable2

WHere [GiftTable2 Amount Pledged] < '25000'

  and [GiftTable2 Campaign Year] < [GIFTTable Campaign Year]

[swuehl]

Where in your script are you using "GIFTTable1 Campaign Year"?

[#10]

Sorry should be gifttable not gifttable1