topic Re: Revenue Expressions in QlikView

Revenue Expressions

Tue, 28 Jun 2011 14:51:29 GMT

I have a question regarding writing an expression that sums revenue for the quarter. I need to sum the total revenue for all the sales of the day in a quarter, and then add them to the total sales for all the previous days of the quarter. The load statement takes the scripts from an excel file, and so there could be 30 sales on day 11 of Q2 FY12, and they are scattered throughout the excel file with no clear organization.

T=Total Sales for the Day; TRTD= Total Revenue to Date (based on day # in quarter).

Example; Day 1 sales( 1A+1B+1C=1T), Day 2 sales( 2A+2B=2T) Day 2 total revenue = 1T+2T=Total Revenue to Date; Day 3 Sales =(3A+3B+3C+3D=3T); Total Revenue = 1T+2T+3T=TRTD….Once I have that expression written, I need to be able to compare the total revenue on say day 45 of this quarter over day 45 of the previous quarter (total revenue for the entire quarter summed for each one), and the total revenue for the current quarter over the same quarter in the previous fiscal year.

Thanks for your help and hope I was clear, and please let me know any further questions you have. Unfortunetly I cannot upload any of the files themselves because they contain confidential data for a publicly traded company.

Alex

Re: Revenue Expressions

IAMDV — Tue, 28 Jun 2011 15:16:58 GMT

Hi Alex,

This is definetly possible. Have you loaded the Master Calendar table from the Load Script which caluclates TRTD/ Till date? If not, then I would recommend loading them in load script and this helps while writing expressions. Also please use Set Analysis inside the variables and then you can re-use the variables for comparison. I hope this helps!

I am also attaching the Master Calendar example (I guess it is Rob's example).

Cheers - DV

Revenue Expressions

Tue, 28 Jun 2011 15:37:28 GMT

Thanks for the quick response DV,

I have a calendar loaded into the load script, its the calendar that our company uses so it includes the necessary fields. It doesn't have the function necessary to calculate the total revenue for the quarter though. I downloaded the master calendar you provided, and didn't see anything about revenue functions in it though. Can I give you the fields I have and you can possible try and write up an example set analysis piece?

I have the fields;

[Total Revenue ($)]

[Fiscal Quarter and Year]: (format is 2011 Q2)

[Day # in FQ].

Any chance you can or anyone else can help me out and provide me with the basics for the set analysis for these fields?

Re: Revenue Expressions

IAMDV — Wed, 29 Jun 2011 09:25:12 GMT

You are welcome

Sure, I will try to squeeze sometime to give you some idea on Set Analysis. I'll try to mock up some expressions... meanwhile if other users can help that will be brilliant. Regarding the previous document I had attached, please ignore that and have a look at the new attachment and look at the variable overiew window for Point in Time reporting. I hope this will give more ideas. Sorry, I had attached wrong document in previous post.

Cheers - DV

Revenue Expressions

Wed, 29 Jun 2011 13:45:11 GMT

Thanks so much DV,

This is great I can't thank you enough. Now, how would i change the expressions to show the total revenue for the quarter only up to the current day in the quarter. Right now I have, which currently shows through day 46 in the current quarter which is the day we are on, but for Q1 FY12, and Q2 FY11 it shows the total revenue through day 89, the end of the quarter...I only am using the one expression below and set the chart over the dimension [Fiscal QTR & Year]. Is this correct?

Sum($(vSetQTD)[TOTAL REVENUE ($)]

Thanks once again,

Alex

Revenue Expressions

Wed, 29 Jun 2011 13:51:02 GMT

Either that or create a button that sets the current date in qtr to 1-CD (ex: days 1-46), as [Day # in FQ] is a current field that I have.

Thanks,

Alex

Re: Revenue Expressions

IAMDV — Thu, 30 Jun 2011 08:14:05 GMT

Hi Alex,

Good morning. I am not sure if you have got this working or if you need more support. I am little busy this morning but I can squeeze in sometime this evening. Do let me know your thoughts.

Cheers - DV

Re: Revenue Expressions

Thu, 30 Jun 2011 12:48:39 GMT

Hey DV,

Still trying to work out this little problem, any support you can give me would be much appreciated!

thanks,

alex

Re: Revenue Expressions

IAMDV — Thu, 30 Jun 2011 13:47:50 GMT

Hi Alex,

Let's work with my example (Demo PITR.QV). Let me understand your need with this document. So now you wanted to calculate Q1 Revenue in this example it will be Sales (Field). Please can you write down list of things you are trying to calculate.

I mean for example something like this...

1. Sales for Q1

2. MTD Sales

3. MTD Previous Month

If you can let me know what you wanted to calculate I can try to give you generic expression which you can use in your QV document. Or else if you are comfortable you can scramble the QV document and then send it to me.

Cheers - DV

Re: Revenue Expressions

Thu, 30 Jun 2011 14:02:41 GMT

Ok I need to calculate

1. Quarter over Quarter Sales; which would be the (total sales for this quarter so far)/(total sales for last quarter)

2. Year over Year Sales; which would be (total sales for this quarter so far)/(total sales for the current quarter 1 year ago) ex: 2012 Q2/2011 Q2.

3. An action or button or bookmark expression that would set the field [Day # in FQ] to 1-CurrentDate for all three quarters. ex: We are on day 46 in the current Fiscal Quarter, so I need all three quarters to only sum their revenues through day 46. I'm assuming the easiest way to do this would be to create some sort of action.

Thanks greatly for all your help,

Alex

Re: Revenue Expressions

IAMDV — Thu, 30 Jun 2011 15:25:03 GMT

Hi Alex,

Here is my approach.

Most important thing to understand is how do you want your end users to access the report. There are two ways...

You can calculate Max Year, Max Quarter and Max Month for the whole document. This way users will always view the latest data. I mean I would use {1} instead {$} where {1} represents full dataset and {$} for current selection.

Now, I am using the first approach to always show current period and previous period.

Step 1: Declaring Variables

I would declare my variable to calculate the CurrentYear, CurrentQTR, CurrentMonth & CurrentWeek. Something like this...

vCurrentQTR = Max({1} QuarterID)

vPreviousQTR = Max({1} QuarterID) -1

Same will be case for all the time dimensions.

*Please note that you can declare these variable in Load Script for better performance instead of declaring within QV document.

Step 2: Expression Building

Now, I would use these variable to create expressions. For example if I wanted to calculate Sales for CurrentQTR & PreviousQTR. I would use the below expressions...

Sales for Current QTR : Sum({$<QuarterID={$(=($(vCurrentQTR)))}>} Sales)

Sales for Previous QTR : Sum({$<QuarterID={$(=($(vPreviousQTR)))}>} Sales)

Again, You can just replace your variable for other time dimensions with same expression.

Let's Assume If you wanted to QTR over QTR Sales then I would use...

Sum({$<QuarterID={$(=($(vCurrentQTR)))}>} Sales) / Sum({$<QuarterID={$(=($(vPreviousQTR)))}>} Sales)

_________________________________________________________________________________________________________________________________

Let's assume that you wanted to give the power to User to select Time Dimensions. I mean if you wanted to calculate the above expression for current selection then simply replace {1} with {$} in your variables.

I had used this approach for many projects. The main advantage is code re-usability and accuracy of expression. I mean my variables are actually underlying expressions and I can control them from one location and re-use them whenever required.

I hope this all makes sense. I understand that this might seem little complex at the beginning...but it will be very easy once you understand approach. Do let me know if you have more questions. I wish I could access some of your sample data to provide QV demonstration

I haven't answered your question to the 3rd point (regarding 46 day in current QTR). I will do that sometime this evening. Meanwhile you have something to work on

Good luck and sorry for the long past... you might need a break now!!!

Cheers - DV

Re: Revenue Expressions

Thu, 30 Jun 2011 17:44:29 GMT

So sorry but I'm having trouble even declaring my variable. In the format you gave,

vCurrentQTR = Max({1} QuarterID)

Should there be a let statement before the v; AND is the quarterID an a made up field that should have been in the load statement already, or should i create an inline table assigning Quarter ID's to each field from my [Fiscal Quarter and Year] statement, which currently has fields formated as ex: 2011 Q2.

Once again so sorry for all the questions.

Alex

Re: Revenue Expressions

IAMDV — Fri, 01 Jul 2011 09:19:27 GMT

Alex - No problem at all. This is why we have the forum... to help each other

Yes, if you are declaring the variables in Load Script you need to use Let/Set keywords at the beginning. In our case we will use SET keyword. So you need use...

SET vCurrentQTR = Max({1} QuarterID);

*Note : We also need to use statement terminator ";" at the end of declaration. However, I should have mentioned this to you earlier. I was assuming that you wanted to declare the variable in "Variable Overview" window, (Ctrl + Alt + V) is the keyboard shortcut.

Also most important thing to understand is the QuarterID is the Field Name, which should be in your data model. I mean the idea is to store your time dimensions like this (Just an example)...

Month MonthID Quarter QuarterID Year

Jan 1 Q1 1 2011

Feb 2 Q1 1 2011

Mar 3 Q1 1 2011

Apr 4 Q2 2 2011

May 5 Q2 2 2011

Jun 6 Q2 2 2011

...............

This is the idea to store the data. If you look at my first post with QV example it contains Master Calendar table in the script as an example. So now you have your Month & Quarter in date format and also in number format. So we are using the number format to calculate for previous Quarter and Previou Month.

I hope this helps!

Let me know if you need more informtion.

Cheers - DV

Re: Revenue Expressions

Fri, 01 Jul 2011 12:35:51 GMT

Unfortunetly I am loading off of an excel file which does not have any of those fields or formats for the date's.

I can tell you what is has for fields and the the format that the field data is organized in.

[Field]; 'Format'

[Fiscal Year]; '2011'

[Fiscal Quarter and Year]; '2011 Q2'

[Date]; '06/07/2010'

[Day # in FQ]; '38'

unfortunetly I have 2000+ sales in the excel spreadsheet, and I do not want to go down and add new columns and formats for each sale. Is there anyway to create an inline table and IF statements to organize the data in the qlikview load statement?

Thanks again,

Alex

Re: Revenue Expressions

IAMDV — Fri, 01 Jul 2011 14:48:37 GMT

Hi Alex,

Absolutely right! We should not be manually updating the Excel Files. Please use the below script and I had also attached a QV document. Please note that you need to change only the values which are in red (both beginning & end of script). You need to set the vDateMin according to your data in your QV document, Identify the minimum date and then same is the case vDateMax.

The below script also contains CalendarQuarterID to calculate the variables/expression for Current Quarter and Previous Quarter. Also, just to let you know that I had gathered this script from Jason Long's post, all credit to him . Although, I had tweaked it little but for our purpose.

Cheers - DV

Calendar:

LET vDateMin = Num(MakeDate(2010,1,1));

LET vDateMax = Num(MakeDate(2011,12,31));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Calendar:

LOAD

Date(TempDate) AS CalendarDate,

// Standard Date Objects

Day(TempDate) AS CalendarDayOfMonth,

WeekDay(TempDate) AS CalendarDayName,

Week(TempDate) AS CalendarWeekOfYear,

Month(TempDate) AS CalendarMonthName,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

Ceil(Month(TempDate)/3) AS CalendarQuarterID,

Year(TempDate) AS CalendarYear,

// Calendar Date Names

WeekName(TempDate) as CalendarWeekNumberAndYear,

MonthName(TempDate) as CalendarMonthAndYear,

QuarterName(TempDate) as CalendarQuarterMonthsAndYear,

// Start Dates

DayStart(TempDate) as CalendarDayStart,

WeekStart(TempDate) as CalendarWeekStart,

MonthStart(TempDate) as CalendarMonthStart,

QuarterStart(TempDate) as CalendarQuarterStart,

YearStart(TempDate) as CalendarYearStart,

// End Dates

DayEnd(TempDate) as CalendarDayEnd,

WeekEnd(TempDate) as CalendarWeekEnd,

MonthEnd(TempDate) as CalendarMonthEnd,

QuarterEnd(TempDate) as CalendarQuarterEnd,

YearEnd(TempDate) as CalendarYearEnd,

// Combo Date Examples

'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,

Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,

'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

LET vDateMin = Num(MakeDate(2010,1,1));

LET vDateMax = Num(MakeDate(2011,12,31));

LET vDateToday = Num(Today());

Re: Revenue Expressions

Wallibee — Mon, 02 Nov 2020 13:52:21 GMT

Hi @IAMDV

I am trying to show turn over figure for some dimensions

Type of organisation

Organisation

Sales channel

Type of customer

I would like to compare this with time dimension for example

last year

last week

Yesterday

How can I calculate this given a Net price and using master Items for easy self service .

Thanks