lookup value based on a calculated measure

[Shilpa16]

Hello Everyone,

I have a requirement where I have actual and planned values for each month and I have to calculate the ratio of  Act/Planned and then I have to compare the % value received with a table where we have different increment values based on different percentages as shown below:

Employee  Act/Plan(the calculated measure)

A                   20%

B                   35%

C                   14%

And the Look up table is :

Percentage       Increment

10%                        100£

20%                         200£

30%                         300£

The user can select time period like a month or qtr ,the values are coming fine if the user select 1 month but if he selects qtr then it is not able to show the correct increment value.Can anyone suggest some solution  please.

Thanks in Advance

Shilpa

[ChiragPradhan]

Try this - Sum(Aggr(Sum(Act), Month))/Sum(Aggr(Sum(Pln), Month))

[Shilpa16]

Thanks will try this.

[Shilpa16]

Hi,

I tried this but it is not working. I need to get the increment values based on my calculated field and that too by any time selection like month, qtr etc

[ChiragPradhan]

The expression I wrote will give you the percentage for any time period selection. You just need to map it to the increment field.

[Shilpa16]

Hi Chirag,

Thanks for your message. Actually, I am able to derive the Percentage values monthly or quarterly but I am having challenge in deriving the increment value based on this calculated Percentage value on the front end. Let me rephrase my query-

MonthYear	Actual	Planned	Act/Planned Percentage
Jan 2018	32	10	(Actual = 32) * 100 / sum (planned for the year = 320) = 10%
Feb 2018	16	25	5%
Mar 2018	25	25	7%(say)
Apr 2018	25	25
May 2018	30	30
Jun 2018	40	40
Jul 2018	25	30
Aug 2018	30	35
Sep 2018	20	25
Oct 2018	20	20
Nov 2018	25	30
Dec 2018	10	15
Jan 2019	20	20

Here denominator is sum of calendar year Planned value

Another Look up table is :

Percentage	Increment
1%	£100
2%	£101
3%	£105
4%	£107
5%	£109
6%	£111
7%	£113
8%	£115
9%	£116
10%	£118
11%	£119
12%	£120
13%	£125
14%	£126
15%	£128
..
..
150%	£225

Suppose now user selects Feb 2018, then the increment should be shown as £109. Now, if user selects Jan 2018 and Feb 2018 (at one go), we should see the result as £128 (as it is 15%), similarly for Feb 2018 and Mar 2018, it should be £120 (as per 12%). Hence user can choose any number of months of a year. How can we apply this lookup as we are deriving increment value based on number of calculated result at the front end.

Please suggest if we need to modify data model to get desired output or can be achieved on the front end itself.

Kindly let me know in case of any queries.

Thanks,

Shilpa

[sasiparupudi1]

Hi Shilpa,

It is better to do this in the script.

Map_Percentages:

Mapping Load

Percentage

Increment

From my lookuptable

;

PlannedValue:

Load

Year,

Sum(PlannedValue)  As YearlyPlannedValue

From your Table

Group By Year;

Map_PV:

Mapping load

Year,

YearlyPlannedValue

Resident PlannedValue

;

Drop table PlannedValue;

Actual_Value:

Load

YearMonth,

Sum(ActualValue),

Sum(ActualValue)/Applymap('Map_PV',Year(YourDate),0) As YourPercent

Resident YourTable

Group By YearMonth

;

Finally you can use the applymap to load incrementals using the  YourPercent column

In the chart, you can then use cumulative sum 

[Shilpa16]

Hi,

Thanks for your solution.

Could you please advice how we can do lookup because if the user select more than 1 month or a qtr  at the frontend then how the lookup will work.

Thanks,

Shilpa

[ChiragPradhan · Accepted solution]

I suppose you want something like the attached. 

Please mark the answer correct, if it is what you are looking for.

Ta

[Shilpa16]

Hi Chirag,

Thank you so much. This is exactly what I was looking for. This was a great help.