topic Set analysis for calculating Growth YoY in QlikView

Set analysis for calculating Growth YoY

Fri, 20 Nov 2020 18:02:30 GMT

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.

Re: Set analysis for calculating Growth YoY

ajaykumar1 — Fri, 12 Sep 2014 05:38:30 GMT

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

Re: Set analysis for calculating Growth YoY

Fri, 12 Sep 2014 05:53:31 GMT

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

Re: Set analysis for calculating Growth YoY

ajaykumar1 — Fri, 12 Sep 2014 06:00:31 GMT

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.

Re: Set analysis for calculating Growth YoY

jagan — Fri, 12 Sep 2014 06:11:42 GMT

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.

Re: Set analysis for calculating Growth YoY

mjayachandran — Fri, 12 Sep 2014 06:18:51 GMT

Check this out,

QlikView App: Set Analysis - Prior Period Comparison

Re: Set analysis for calculating Growth YoY

Fri, 12 Sep 2014 06:22:04 GMT

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

Re: Set analysis for calculating Growth YoY

vikasmahajan — Fri, 12 Sep 2014 06:39:20 GMT

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

Re: Set analysis for calculating Growth YoY

Fri, 12 Sep 2014 06:42:59 GMT

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

Re: Set analysis for calculating Growth YoY

jagan — Fri, 12 Sep 2014 06:47:06 GMT

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.

Re: Set analysis for calculating Growth YoY

Fri, 12 Sep 2014 07:24:46 GMT

create variable like this:---

vMaxYear =max(Year)

vPriorYear =vMaxYear -1

vMaxDate =max(Date)

vMinDate =min(Date)

vPriorYearDate_max =date(addyears(max(Date), -1), 'DD MMM YYYY')

vPriorYearDate_min =date(addyears(min(Date), -1), 'DD MMM YYYY')

then try this

= SUM({<Year={'$(vMaxYear)'},Date={'>=$(vMinDate)<=$(vMaxDate)'},Month= >}Sales)

SUM({<Year={'$(vPriorYear)'},Month=,Date={'>=$(vPriorYearDate_min)<=$(vPriorYearDate_max)'}>}Sales)