Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a combo chart. X axis are quarters of year, Q1 2012, Q2 2012 etc... Y axis are sum(Revenue).
I would like to add a dimension that is the growth from one quarter over the next. Calculation is not possible for the first quarter in the data. then formula would be LAST(Sum(revenue))/FIRST(Sum(Revenue)). how can i refer to previous quarter of year in my expression ? how can i check if data for the last quarter of year are available ?
Regards
Try below expression, may be it works,
if(isnull(before(sum(revenue))),0,(sum(revenue)/before(sum(revenue))))
if it do not work , then try to use above instead of before.
Regards
Nitin
thanks
neither is working
Can you please try it in straight table first,
Right now I dont have QV on my system, as I am out of Office, therefore I cannot check it on QV.
Try using ABOVE or BELOW depending on how your Year-Quarters are sorted. The BEFORE function will return NULL in all chart types except pivot tables. If you want the first Quarter in your data set to display something besides 0 for growth for some reason, you could replace the 0 in my first expression below with an additional expression. If you are trying to use LAST or FIRST for this piece, use BOTTOM and TOP instead.
=alt(sum(Revenue)/above(sum(Revenue)),0)
=alt(sum(Revenue)/above(sum(Revenue)),bottom(Sum(Revenue))/top(Sum(Revenue)))