Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Thank you so much!!!
no problem. close the thread so that others can see the answers too
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:
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?
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
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!!
Try this
Aggr(Sum(Sales)/Above(Sum(Sales), 12) - 1, (BillingMonthYear, (NUMERIC)))
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!!