topic Re: What is wrong with this formula? in QlikView

What is wrong with this formula?

montubhardwaj — Mon, 05 Sep 2011 07:40:07 GMT

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

Re: What is wrong with this formula?

its_anandrjs — Mon, 05 Sep 2011 07:51:22 GMT

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

What is wrong with this formula?

montubhardwaj — Mon, 05 Sep 2011 08:03:48 GMT

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.

What is wrong with this formula?

Or — Mon, 05 Sep 2011 08:13:26 GMT

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..

What is wrong with this formula?

kaushalview — Mon, 05 Sep 2011 08:17:04 GMT

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.

Load

Policy,

Sum(sales) as Sales

From Table1 Group by Policy;

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

What is wrong with this formula?

its_anandrjs — Mon, 05 Sep 2011 08:17:55 GMT

Hi,

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

Regards,

Anand

Re: What is wrong with this formula?

its_anandrjs — Mon, 05 Sep 2011 08:27:30 GMT

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

Re: What is wrong with this formula?

SunilChauhan — Mon, 05 Sep 2011 08:50:02 GMT

hello sharma,

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

see the attached file

hope this helps u

Re: What is wrong with this formula?

SunilChauhan — Mon, 05 Sep 2011 08:54:14 GMT

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

Re: What is wrong with this formula?

its_anandrjs — Mon, 05 Sep 2011 08:55:36 GMT

Hi,

See the attached updated sample might it will work for you

Hope this helps you.

Regards,

Anand

What is wrong with this formula?

montubhardwaj — Mon, 05 Sep 2011 08:57:09 GMT

Thank You all for your response. I forgot to do group by. BUt let me know one thing: if I dont put Sum in the script, it runs fine. But will it be right to do so? Anywway, I am putting Sum(<field>) in the expression. Thanks for your response.