Set analysis for calculating Growth YoY

[#1]

Hi,

I have a list box of year and month, on selecting for example February 2014 and March 2014 , it should show me (sum of sales in Feb +March2014  ) -(sum of sales in Fwb +March2013 ) for the previous year. how do i write set analysis expression for it. Also, is there any reference where i can learn to write these expressions and get a know about functions used in qlikview.

Thanks in advance.

[ajaykumar1]

Hi,

Try like this;

SUM({<PERIOD={"$(=MAX(PERIOD))"}>}Sales)

-

SUM({<PERIOD={"$(=MAX(PERIOD)-1)"}>}Sales)

Here PERIOD means your year in master calender.

Regards,

Ajay

[#3]

Well, in period i want month as well as year, how will it be done ?

[ajaykumar1]

Hi,

Try like;

SUM({<Month = {"$(=max(Month))"},PERIOD={"$(=MAX(PERIOD))"}>}Sales)

-

SUM({<Month={"$(=MAX(Month)-1)"},PERIOD={"$(=MAX(PERIOD)-1)"}>}Sales)

Share your sample app .I will check.

[jagan]

Hi,

Try like this

Script:

LOAD

*,

MakeDate(Year, Month) AS Date

FROM DataSource;

Now in Set Analysis

SUM({<Year=, Month =, Date= {">=$(=MonthStart(Max(Date)))<=$(=MonthEnd(Max(Date)))"}>}Sales)

-

SUM({<Year=, Month =, Date= {">=$(=MonthStart(Max(Date), -1))<=$(=MonthEnd(Max(Date), -1))"}>}Sales)

Hope this helps you.

Regards,

Jagan.

[mjayachandran]

Check this out,

QlikView App: Set Analysis - Prior Period Comparison

[#7]

it shows only the sales value in growth and doesnt calculate anything on single/multiple selection of months .

[vikasmahajan]

CY :

SUM({$<FiscalYear={$(=max(FiscalYear))},Quarter=

,[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]))"}>}[Sales Amount])

LY:

Sum({$<FinancialYear=,FiscalYear={$(=max(FiscalYear)-1)}

,[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-1,4))<=$(=addmonths(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-12))"}

>} [ Sales Amount])

for Growth

((Column(1)-Column(2))/Column (2))*100

Hope this helps you

Vikas

[#9]

Can you please attach your sample qvw file.

Moreover, looking at the screenshot, it seems like in Growth column you need like

2013 >> 0

2014 >> Value(2014)-Value(2013)

So you have to use Above() function in the chart expression to achieve this.

Thanks,

Singh

[jagan]

Hi,

Try like this

Script:

LOAD

*,

MakeDate(Year, Month) AS Date

FROM DataSource;

Now in Set Analysis

SUM({<Year=, Month =, Date= {">=$(=MonthStart(Min(Date)))<=$(=MonthEnd(Max(Date)))"}>}Sales)

-

SUM({<Year=, Month =, Date= {">=$(=MonthStart(Min(Date), -12))<=$(=MonthEnd(Max(Date), -12))"}>}Sales)

Hope this helps you.

Regards,

Jagan.