This is my sample sales data.

Sales_amount by Product and Sold_on date.

Requirement is Report has to be created CURRENT_MONTH_SALES, PREVIOUS_MONTH_SALES by PRODUCT wise.

temp.PNG

So Expected sample report is,


temp.PNG

This can be achievable by creating Restricted Measures. But Limitations are there.

Limitations:

If we create Restricted Measures – CURRENT_MONTH_SALES and PREVIOUS_MONTH_SALES withstatic values as condition,

Models has to be changed for every month.

temp.PNG


Else If we create Restricted Measures – CURRENT_MONTH_SALES and PREVIOUS_MONTH_SALES with Input Parameters as condition,

User has to enter Current_Month and Previous_Month Value whenever he check the report.

temp.PNG


How To Make Restricted Measures Calculation Dynamic?


So Restricted Measures – CURRENT_MONTH_SALES and PREVIOUS_MONTH_SALES has to be calculated dynamically based on report execution date.

How to make CURRENT_MONTH_SALES and PREVIOUS_MONTH_SALES value to be Dynamic?


This is the Steps I have followed to achieve this:

1. I created Calculation view with default node as Aggregation node. Then I included Projection node and connected it to Aggregation node.

    I added the “SALES_DATA” table into Projection node.

          temp.PNG

2. In Projection node, I have created one Calculated Column – “MONTH”

    “SOLD_ON” is DATE field. From this field, Month is calculated.

     temp.PNG

3. In Aggregation node, I have created two Restricted Measures – CURRENT_MONTH_SALES and PREVIOUS_MONTH_SALES

 

     temp.PNG

      In this Restricted Measures, Conditions will be dynamic and It is written in Expression editor.

    

     RM: CURRENT_MONTH_SALES:

          Restriction Condition: (“MONTH” = Int(midstr (string(now()), 6, 2)))

          Current_Month calculated using “midstr” and “now()” functions.

          Then Restriction condition is applied on “MONTH” Column.

        

     temp.PNG

     RM: CURRENT_MONTH_SALES:

        

          Restriction Condition: (“MONTH” = Int(midstr (string(now()), 6, 2))-1)

          Previous_Month calculated using “midstr” and “now()” functions and Substrcted with “1”.

          Then Restriction condition is applied on “MONTH” Column.

          temp.PNG

   

4. Finally I created the report – CURRENT_MONTH_SALES, PREVIOUS_MONTH_SALES by PRODUCT wise.

    CURRENT_MONTH_SALES, PREVIOUS_MONTH_SALES would be calculated dynamically based on report execution date.

    No need to change the model for every month.

     temp.PNG

          temp.PNG

Best Regards,

Muthuram

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Jalina Vincent

    Hi Muthuram,

    Thanks for providing wonderful document with scenario.

    I am facing one issue while doing this scenario.

    As per your example, I have created the Calculated column “Month”. After this, i am creating restricted Column (CURRENT_MONTH_SALES) and i am trying to use Calculated Column “Month”. But it is not appearing here. Please find the Snap for your reference.

    Doubt : Can we use calculated Column in Restricted Column.

    Thanks.

     

    (0) 
  2. Jalina Vincent

    Hi,

    I got the solution. I didnt select Calculated colunm(“Month”) in Projection View. Due to this, I am unable to use it in restricted. After enabling, I can use calculated Column in Restricted column of Aggregation.

    Thanks.

    (0) 

Leave a Reply