Compare Sales for Current Period vs Previous Period

[inessa_zhurba]

This is what I am trying to do. I have a report below is a picture.

Percentages suppose to show by month increase of Sales.

Underlying data contains 3 years worse of data.

I was try to get data from sql. I am not sure how to do it in qlik.  Would you share a script with me?

Thanks,

Inessa.

[dplr-rn]

Use before function.

e.g. (Sum(Sales) - before(Sum(Sales)) ) / before(Sum(Sales))

so in Feb column before(Sum(.... gives value of sum from jan

check below for more details

Missing Manual - Before() and After()

[inessa_zhurba]

Thank you so much!!!

[dplr-rn]

no problem. close the thread so that others can see the answers too

[inessa_zhurba]

Hello again - It works as a charm for all months started with February(month #2). It doesn't look like it works for the month #1 (January). It should compare to December previous year - but there are blanks on a report:

[dplr-rn]

that becomes a bit more convoluted

check below. basically what i am doing to is if its first column go to last column in previous line. Also simplified function (A-B) /B as A/B -1

if(ColumnNo()=1,Sum(Sales)/above(after(Sum(Sales),11)) -1, Sum(Sales)/before(Sum(Sales)) -1)

stalwar1‌ any straightforward way of doing this with set analysis?

[sunny_talwar]

I would use Aggr() with MonthYear field to get this

Aggr(Sum(Sales)/Above(Sum(Sales)) - 1, (BillingMonthYear, (NUMERIC)))

Where BillingMonthYear is created in the script something in this fashion

Date(MonthStart(DateField), 'MMM-YYYY') as BillingMonthYear

[inessa_zhurba]

Thank you Sunny and Dilip both very much!! Both approaches are working!!!

I have a new challenge - I need to compare This Year Month to a Previous Year Month

And I have a Numeric field that Identifies a BillingPeriod. Example:

BillingPeriodID 201501 stands for January of 2015

I was trying to mimic last post by Sunny:

Aggr(Sum(AmountBilled)/Above(Sum(AmountBilled)) - 12, (BillingPeriodID, (NUMERIC)))

It didn't work though!

Here how report looks like:

Would you be able to help me with the script?

Thank you!!

[sunny_talwar]

Try this

Aggr(Sum(Sales)/Above(Sum(Sales), 12) - 1, (BillingMonthYear, (NUMERIC)))

[inessa_zhurba]

Hi Sunny - thank you very much for your reply! Unfortunately it doesn't work.

Here is a data that I have by Year and month:

This is what needs to happen:

For example for Month of January 2016 (BillingPeriodID = 201601) it should display data from January 2015 (BillingPeriodID = 201501) (Previous Year and the same Month Number)

I was trying to play with it - no success! Would you be able to take a second look, please?

Thank you!!