If statement not working in load script

[simospa]

I have a script that build some fileds calculating in order to different contition.

I try to do this:

LOAD x,y,z,

sum(IF (Month='8' and Year=year(Today(0)),  IF(Month<Month(Today()),  IF(TabName = 'CX',   CX_Liters,0)  ,  IF (TabName = 'B1',   B1_Liters ,   IF (TabName = 'B0' ,   B0_Liters ,  IF (TabName = 'B4' ,   B4_Liters ,   IF (TabName = 'B3',   B3_Liters  )  )  )

)),0)) as Actual_Agoust

...

FROM myqvdfile.qvd

GROUP BY x,y,z

I want this behavior: if the month value is August and year is 2004, then if month value is minor than the actual (in this case August) I want check tableName="CX" and sum CX Liters. Otherwise, if month is not minus of actual month, I want to test Table Name: If B1, then sum B1_Liters, ELSE if B4 then sum B4 Liters, ELSE if B3 then sum B3_Liters in this order.

My big problem is that the script provide to sum either B1, AND B3, AND B3... while I want that if it finds B1 value it sum only B1_Liters and not others (B4, B3).

Can anyone help me please?

Simone

[mrooney]

Hi Simone,

not sure if I understand the problem quite right.

I think that you want this:

sum(IF (Month='8' and Year=year(Today(0)),

   IF(Month<Month(Today()), // Here because Mont = 8 and Year = actual year

     IF(TabName = 'CX',   CX_Liters,0) , // here because TableName = 'CX'

       IF (TabName = 'B1',   B1_Liters ,   // Here because Table Name = 'B1'

        IF (TabName = 'B0' ,   B0_Liters , // Here because TableName = 'B0'

          IF (TabName = 'B4' ,   B4_Liters ,  // Here because TableName = 'B4'

            IF (TabName = 'B3',   B3_Liters) // Here because TableName = 'B3'

           )

          )

         )

        )

       )

      )   as Actual_Agoust.

Hope helps

[simospa]

Thansk Miguel, but it doesn't works... the behaviuor is the same in my original statement.

I want that, if I find B1, it doesn's sum B0, B4, B3 too...

S.

[mrooney]

Please send me a test example. It seems to me the expression should work..

[simospa]

I'm agree, technically it works as work mine... pheraphs the problem is instead in graph expression, where I use set analisys.

I have

sum( {$<year= {$(actual_year)}, Mese = {8}>} actual_agoust)

but I get all the results for August. But I want that, in this order, if sum for tablename=''B1" is zero then get sum for tablename='"B0", if this is zero then get the sum for tablename="B4" and if zero then get sum for tablename="B3".

In this order... exists a way to manage this behaviour with set analysis?

Thanks

S.

[mrooney]

Well,

I think that if you want to add amount from different tables, the you'll need to define different accumulaion fields for each one in your script.

After that, in the chart you can manage this situation with set analysis.

sum( {$<year= {$(actual_year)}, Mese = {8}>} (If B1Acumm >0, B1Acum, If....., if....))

[simospa]

Nice idea, so I can store value in different columns and use it as you suggest...

I'll try...

Thanks, I tell you asap.

S.