Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
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..
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
Hi,
Do you have any sample file for that i thing you want rolling 12 months.
Regards,
Anand
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
hello sharma,
I belive u require rolling 12 mnths calculation,if yes then
see the attached file
hope this helps u
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
Hi,
See the attached updated sample might it will work for you
Hope this helps you.
Regards,
Anand