Skip to Content
Author's profile photo Muthuram Shanmugavel
Muthuram Shanmugavel
April 13, 2016 3 minute read

Restricted Measures Calculation – Make it Dynamic

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

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo vara prasad Puram
      vara prasad Puram

      Hi Muthuram,

      Thanks for the nice document. But the logic is failing for the below scenarios. Can you explain me how can we eliminate the issue.

      Example: I had taken same scenario with the below example data.

      I implemented the same logic for PREVIOUS_MONTH_SALES as for my sample data. But the logic failing to get the previous month data.

      I have used the same expression as you used for getting the previous month data

      Int(midstr(string(now()),6,2)-1) it's giving 0 instead 12

      its calculating as 1-1=0.

      Regards,

      VaraPrasad.