topic Re: About Financial Year in QlikView

About Financial Year

sarfaraz_sheikh — Thu, 29 Jan 2015 11:47:36 GMT

Dear All,

I have the following format i want to calculate like below.

Product FY1 FY2 %growth

A 100 150 50%

B 120 150 25%

Year(YearStart(Max(Date-1),0,4) )-1 &' -'& Year(YearStart(Max(Date) ,0,4)) As FY1

Year(YearStart(Max(Date-1),0,4) ) &' -'& Year(YearStart(Max(Date) +1,0,4)) As FY2 or Current_fin_year

However Whenever i am reloading ...i am getting same error masage in above two lines "scrip Line Error " ...What wrong i have done i am unble to find .

Kindly help to achieve the same!!!!!!

Sarfaraz

Re: About Financial Year

rubenmarin — Thu, 29 Jan 2015 11:52:38 GMT

Hi Sarfaraz, when you use Max(Date) you need to load with a group by clause, in this 'group by' you need to put all field that are not aggregated, ie:

LOAD Company,

Customer

Max(Date) as Date,

Sum(Sales) as Sales

From ...

Group by Company, Customer; // Date and Sales are aggregated, so they aren't in the Group by

Re: About Financial Year

sarfaraz_sheikh — Thu, 29 Jan 2015 11:56:25 GMT

The abobe expression which i wrote ...is that write to calculate the financial year as per format.

Sarfaraz

Re: About Financial Year

PrashantSangle — Thu, 29 Jan 2015 11:58:44 GMT

Hi,

Use YearName()

Try like

YearNAme(date,0,4)

REgards

Re: About Financial Year

cesaraccardi — Thu, 29 Jan 2015 11:58:48 GMT

Hi Sarfaraz,

I think it could be an issue with the Max() aggregation function, do you have a GROUP BY clause on your load statement? If so, are you listing all fields that are being loaded?

Cesar

Re: About Financial Year

rubenmarin — Thu, 29 Jan 2015 12:08:24 GMT

Hi Sarfaraz, I only posted a simple example so you can see the group by clause, that error message usually is related to the use of an aggregation function (Max, Min, Sum...) without a fully correct 'Group By' clause