How to prompt for Quarters (Q1, Q2, Q3, and Q4) in BusinessObjects

How to prompt for Quarters (Q1, Q2, Q3, and Q4) in Business objects using designer?

Simple Method…
You can use @ Variable function to prompt for Quarter. Here is one example.

TO_CHAR (CALENDAR_DATE,'Q') =@Variable('Which Quarter?')

But Coding can be little complicated when users want to select Q1,Q2,Q3,Q4 from the list, instead.
In that case Syntax should be something as below

( CASE WHEN TO_CHAR(CALENDAR_DATE,'Q') ='1'
THEN 'Q1'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '2'
THEN 'Q2'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '3'
THEN 'Q3'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '4'
THEN 'Q4' END) =@Prompt('Which Quarter?','A',{'Q1','Q2','Q3','Q4'},mono,Constrained))

Now let’s assume requirement is to select year and quarter and retrieve date up to next 36 months... In this example I am going to use @variable as well as @Prompt.

TABLE.CALENDAR_DATE BETWEEN (SELECT Distinct MIN(CALENDAR_DATE) from CALENDAR_DIM
WHERE TO_CHAR(CALENDAR_DATE,'YYYY') =@Variable('Which Year?')
AND ( CASE WHEN TO_CHAR(CALENDAR_DATE,'Q') ='1'
THEN 'Q1'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '2'
THEN 'Q2'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '3'
THEN 'Q3'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '4'
THEN 'Q4' END) =@Prompt('Which Quarter?','A',{'Q1','Q2','Q3','Q4'},mono,Constrained))
AND
(SELECT Distinct MIN(LAST_DAY(ADD_MONTHS(CALENDAR_DATE,35))) from CALENDAR_DIM
WHERE TO_CHAR(CALENDAR_DATE,'YYYY') =@Variable('Which Year?')
AND ( CASE WHEN TO_CHAR(CALENDAR_DATE,'Q') ='1'
THEN 'Q1'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '2'
THEN 'Q2'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '3'
THEN 'Q3'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '4'
THEN 'Q4' END) =@Prompt('Which Quarter?','A',{'Q1','Q2','Q3','Q4'},mono,Constrained))

Fun with Xcelsius

I used my Facebook friends profile to create a simple dashboard using Xcelsius BI reporting tool. The purpose of this dashboard is just for fun and i believe friends privacy has not been compromised ! Have fun ! 

Section Access (Qlikview)

Qlkview is a Business Intelligence reporting tool and this tool is really gaining momentum in US job market. This tool has many cool features that can be leveraged as per your BI needs. On this blog i am going to share QlikView security feature that  might come handy when data cannot be compromised. 

example :

 Let's assume that we have two users; UserA, UserB. UserA is responsible for product A and UserB is responsible for product B. Our reporting requirement is to write a report for both products. However; we also want to secure data in such a way that product A is not visible to UserB and Product B is not visible to UserA.

In order to secure data, we can use a Qlikview feature called section access. Section access helps you protect data from unauthorized access as well as limit authorized users access to important data.

With the help of section access, you can restrict unauthorized access or limit authorized access  in different ways. For instance you can either use basic user access approach or basic NT security approach for access restriction. For our example purpose we will use basic user access approach.

Before writing script for section access, let's make sure “Initial Data Reduction Based on Section Access”  and "strict exclusion" is checked under Settings > document properties > Openings.  

Now let's start writing section access script.....

In order to maintain security, we will create hidden script for section access.  (File > Create hidden script. )

Section Access;  /* Start section access script */

LOAD * INLINE [

    ACCESS, USERID, PASSWORD, PRODUCT

    ADMIN, ADMIN, ADMIN

    USER, USERA, U1, A

    USER, USERB, U2, B  

];

Section Application; /* End section access script */

PRODUCT:

LOAD * INLINE [

PRODUCT, PROFIT

A, 1000

B, 2000

];

In the above example we created row level security that means userA will be able to see profit only for his product. Similar UserB will be able to see profit only for his product.

Section access can be used not only for row level security but also for column level security. The advantage of section access as shown in our example is that it eliminated the need for creating two reports for each product. In a large organization where we have thousands of products and their related data cannot be shared among users, in that case,  section access can be very useful.

ABCD Stratification

Let's assume that we have two items. One item cost 10K and other one cost $10. Also, let's assume that both items have high coefficient of variation that means both items are critical items. In this situation it makes sense to prioritize and  investigate root cause for high volatility for expensive item rather than cheap one .

When you are managing hundreds of item then it might be difficult to identify those items, which are expensive and highly volatile and need immediate attention. On this blog i will show you how to mathematically identify items that need immediate attention. The kind of items that need immediate attention are those items that have not only high CV but also are expensive. As i have showed you earlier how to calculate CV, on this blog i will show you how to identify items that can create panic.

ABCD Stratification : ABCD Stratification method categorize items into A,B,C, and D based on weighted percentage of item cost (For our example purpose we are using items cost. Otherwise It could be anything like inventory cost, Average selling price,  or Billing Unit).

Note that Item that has high CV and fall under "A" category should be given high priority.

Here is how we calculate ABCD. Note that you should calculate ABCD for entire product lines.

1. Sort cost (Qty) in descending Order

2. Calculate Total Qty

3. Divide each cost by total Qty (Calculate Weight)

4. Do cumulative sum of  weight (You will need to do cumulative sum on the result from step 3)

5. For our example purpose, if cumulative sum is less than 80% then we will consider as A item. If cumulative sum is between .80 and .95 then we will consider as B item. If cumulative sum is between .95 and .99 then we will consider as C item and rest would be D items.

You will notice that items that fall under "A" have high Qty. If an item has high CV and falls under category A then you should give high priority to that item. 

Finding volatile items using SQL

In my last blog we i showed you how to identify volatile items with help of simple mathematical calculation. Today i am going to show you how to identify volatile items using SQL.

First we will need to create table in Oracle. This time we are not going to write sql to create and insert data into table. This time we will use oracle application builder to create new table and add data from external source (.xls).

As appose to traditional SQL method, creating a table and adding records to it is fairly simple if you use application builder. You can either copy and paste your data directly to application builder or you can import to it. Once we create table and add data from external source, we are ready to write SQL to calculate Standard deviation and average of each rooms.

 Select Distinct Room, STDEV,  AVERAGE from
(select Period_date,
Room, Qty,
Round(STDDEV(QTY) Over(Partition by Room),2) STDEV,
Round(AVG(QTY) OVER(Partition by Room),2) as AVerage
from hotel ) CV_CALC ;

ROOM                           STDEV                  AVERAGE               

------------------------------ ---------------------- ----------------------

Embassy                       27.37                  22.33                 

Honeymoon                 36.41                  48.5                  

Apartment                    3.61                   6.5                   

Classic                            0.89                   10.67                 

Presidential                   3.75                   6.42    

Since we calculate standard deviation and average, it is now time to calculate coefficient of variation. We will be using coefficient of variation to identify product volatility.

Select Room,
Round((STDEV/AVERAGE),2) as CV,
Case when  Round((STDEV/AVERAGE),2) > 1 then 'High'
When  Round((STDEV/AVERAGE),2) between .50 and 1 then 'Med'
when  Round((STDEV/AVERAGE),2) < .50 then 'Low'
end as CV_Volatility
from
(Select Distinct Room,STDEV, AVERAGE from
(select Period_date,
Room, Qty,
Round(STDDEV(QTY) Over(Partition by Room),2) STDEV,
Round(AVG(QTY) OVER(Partition by Room),2) as AVerage
from hotel ) CV_CALC ) CV ;

ROOM                           CV                     CV_VOLATILITY

------------------------------ ---------------------- -------------

Embassy                        1.23                   High         

Honeymoon                  0.75                   Med          

Apartment                     0.56                   Med          

Classic                             0.08                  Low          

Presidential                   0.58                   Med          

That is it for now. Will see you in next blogs.

Finding highly volatile products

Finding highly volatile products (Hotel Suites)

When you have hundreds of product in your portfolio then it can be tricky to find out products with unpredictable demand. For our calculation we will take an example of occupancy ratio of hotel rooms. Let's say you are a manager of a hotel and you are managing 5 different suites of room; Embassy, Honeymoon, Presidential, Apartment, Classic. Being a manager, it is your responsibility to find out which suite has highly unpredictable demand. In other words, you will need to calculate demand volatility of hotel suites.  

Hypothesis 1. :- None of the hotel suites is recently introduced. (If hotel suites are recently introduced then we need to tweak our calculation, therefore, for example purpose we are going to assume that none of the suites is newly created)

Hypothesis 2. There are 20 rooms assigned for each suites. (20*5=100 total rooms )

We will first start calculating occupancy ratio.

Example to calculate occupancy ratio for a week :-  

Here is a simple example of occupancy room nights for a suite:
20 rooms available for a suite
10 rooms sold on Monday = 50% occupancy rate for that day
15 rooms sold on Tuesday = 75%
16 on Wednesday = 80%
20 on Thursday = 100%
19 on Friday = 95%

20 on Saturday =100%

10 on Sunday =50%
Total available room for the 7 day period = 140 (7 * 20=140)
Room nights sold in a week =  10+15+16+20+19+20+10 = 110
110/140 = 80% occupancy rate for a week

Based on above example we came up with following occupancy rate for each suites for each month for last year.

Room /Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Embassy	10	12	13	24	34	0	0	0	0	23	76	76
Honeymoon	12	23	34	54	56	67	100	100	100	10	12	14
Presidential	0	2	3	4	5	6	7	8	9	10	11	12
Apartment	1	2	3	4	5	6	7	8	9	10	11	12
Classic	10	10	10	11	10	12	10	11	10	12	12	10

Now it is time to calculate volatility of suites. In order to find our answer we need to calculate coefficient of variation (CV) of occupancy rate. 

CV :- Standard Deviation of Occupancy rate for last year / Average Occupancy rate for last year

Room /Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	AVG	ST DEV	CV
Embassy	10	12	13	24	34	0	0	0	0	23	76	76	22.3	27.371	1.23
Honeymoon	12	23	34	54	56	67	100	100	100	10	12	14	48.5	36.411	0.75
Presidential	0	2	3	4	5	6	7	8	9	10	11	12	6.4	3.753	0.58
Apartment	1	2	3	4	5	6	7	8	9	10	11	12	6.5	3.606	0.55
Classic	10	10	10	11	10	12	10	11	10	12	12	10	10.7	0.888	0.08

Highly unstable items are the one with high CV. Actually, anything more than 1 CV is highly unstable, greater than .50 is medium and less than .50 is stable.

Please note that, decision making purely based on Coefficient of variation is not the correct approach. The first thing we need to consider is cost factor. I will write more on this topic (combining ABC stratification and Coefficient of variation ) in my next blog. Also, I will show how to code them using SQL.