Total Revenue Expressions

[#1]

Hello,

I need to create a line graph that shows the rolling total revenue for the quarter, as well as the previous quarters total revenue, and the previous year quarter's total revenue( meaning if we are in FY12 Q2, I need line's for FY12 Q2, FY12 Q1, and FY11 Q2.)

Can anyone please help me write the expressions for this graph?

Thanks,

Alex

[#2]

Alex,

Set Analysis is what it sounds like you want to use in your expression here. Try something like this:

Current Quarter:

Sum({$<[Fiscal Quarter]={$(=max([Fiscal Quarter]))}>} [Net Revenue])

for last quarter do:

Sum({$<[Fiscal Quarter]={$(=max([Fiscal Quarter])-1)}>} [Net Revenue])

for a year ago do:

Sum({$<[Fiscal Quarter]={$(=max([Fiscal Quarter]))}>} [Net Revenue])

[#3]

Thanks for the response leonard.  I just have a few quick questions.  What's the difference between the year ago expression and the current quarter expression?  Should I put a -4 where the -1 is in the second expression?  Also, is the net revenue something you just made up? 

Also, I did not explain the problem very clearly.  I need to show linearity charts that show the total revenue for the quarter based on [Day # in the QT], which is a field I already have in qlikview.

The problem is the excel spreadsheet I have loaded into qlikview is not organized, and instead the data rows are compromised of individual sales. So there could be 40 rows of sales all representing a sale on day 38 in the quarter, and I need to total these, and then add Day 38's total sales to all the previous day's sales.

Sorry for the confusion, I realize this is a tough problem but any ideas that would set me down the right track are greatly appreciated.  Thank you,

Alex

[#4]

Sorry about the year ago, yes if you put a -4 in there it will give you 4 quarters ago, which would not be the same quarter a year ago, for that you would need to do a -5. Depending on how your data is setup you may need to do something like this to get the same quarter for the previous year:

sum([$<Quarter = {$(max(Quarter))],Year ={$(=max(Year)-1}>} Sales)

This will sum all Sales that occured in the final quarter of last year.

[Net Revenue] & [Fiscal Quarter] are both just field names. Replace them with whatever you need to use within your dataset. 

It shouldn't matter how the data sits in excel, just load each row into QV & generate some date fields in the load,

i.e. load date,

         quarter(date) as quarter,

          year(date) as year,

          day(date) as day,

          month(date) as month,

          sales_id,

         sales

from sales.xls

Search for 'Set Analysis' in the QV help screens for more examples/syntax of what you can do as it is a very very useful function!