What is wrong with this formula?

[montubhardwaj]

Hi All,

The below formula is working all fine when I am using it in the expression of a chart. But when I am trying to calculate it in the script, its throwing an error saying "invalid exoression". Can you please explain what is the error ?

If(SlsYear=2011 and SlsMonth<9,Sum(Sales),If(SlsYear=2010 and SlsMonth>=9,Sum(Sales))) as Roll_12_Sales

Cheers,

Sharma

[its_anandrjs]

Hi,

In which type of table you use it it is resident or what if it is main table so i suggest you to try to load a resident table of the main table and o that try like

Temp:

Load

....

...

From Table;

Data:

Load

*,

If(SlsYear=2011 and SlsMonth<9,Sum(Sales),If(SlsYear=2010 and SlsMonth>=9,Sum(Sales))) as Roll_12_Sales

Resident Temp;

Regards,

Anand

[montubhardwaj]

I am pulling this table from a Qvd and directly writing this formula in the table. I have all the variables defined and all the fields are present in the table. And I am writing this formula as a last field in the table. So I dont think I am wrong there. Please explain.

[Or]

I am going to take a stab and guess that SlsYear and SlsMonth are fields that only came into existence during the current load, which means you can't use them in that expression (you'd have to use the long form). However, without the actual code, it's impossible to say for sure..

[kaushalview · Accepted solution]

Hi,

if you are using Sum(Sales) then you have to use Groupby in the table or qvd.becaz sum(Sales) is aggra function in the script.

A:

Load

Policy,

Sum(sales) as Sales

From Table1 Group by Policy;

B:

Load *,

If(SlsYear=2011 and SlsMonth<9,Sales,If(SlsYear=2010 and SlsMonth>=9,Sales)) as Roll_12_Sales

Resident A;

Drop table A;

Try this.

Regards

Kaushal mehta

[its_anandrjs]

Hi,

Do you have any sample file for that i thing you want rolling 12 months.

Regards,

Anand

[its_anandrjs]

Hi,

See the attached sample i think it is ok for you I think you have to use some thing like this.

Temp:

LOAD SlsYear,

     SlsMonth,

     Sales

FROM

..\Data.xlsx

(ooxml, embedded labels, table is Sheet22);

Load *,

If(SlsYear=2011 and SlsMonth<9,Sales,If(SlsYear=2010 and SlsMonth>=9,Sales)) as Roll_12_Sales

Resident Temp;

Drop table Temp;

And Let me know

Regards,

Anand

[SunilChauhan]

hello sharma,

I belive u require rolling 12 mnths calculation,if yes then

see the attached file

hope this helps u

[SunilChauhan]

If(SlsYear=2011 and SlsMonth<9,Sum(Sales),If(SlsYear=2010 and SlsMonth>=9,Sum(Sales))) as Roll_12_Sales

requires group by by all fields which are not used in any aggregation fuction.

invalid expression error always due to missing group by.

hope this helps you

[its_anandrjs]

Hi,

See the attached updated sample might it will work for you

Hope this helps you.

Regards,

Anand