This field is required.
Only these extensions are allowed(.jpg, .JPG, .jpeg, .JPEG, .gif, .GIF, .png, .PNG)
Tags cannot contain the characters ' /, \\, #, ?, or ; >,< '
Only these extensions are allowed(.zip,.ZIP,.pdf,.PDF,.qvf,.QVF,.qvw,.QVW)
Discussion Board for collaboration related to QlikView App Development.
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?
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()
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?
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?