Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
inessa_zhurba
Contributor III
Contributor III

Compare Sales for Current Period vs Previous Period

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.

19 Replies
dilipranjith
Partner
Partner

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
Contributor III
Contributor III
Author

Thank you so much!!!

dilipranjith
Partner
Partner

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

inessa_zhurba
Contributor III
Contributor III
Author

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:

dilipranjith
Partner
Partner

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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