Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Shilpa16
Contributor III
Contributor III

lookup value based on a calculated measure

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

1 Solution

Accepted Solutions
ChiragPradhan
Creator II
Creator II

I suppose you want something like the attached. 

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

Ta

View solution in original post

10 Replies
ChiragPradhan
Creator II
Creator II

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

Shilpa16
Contributor III
Contributor III
Author

Thanks will try this.

Shilpa16
Contributor III
Contributor III
Author

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
Creator II
Creator II

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
Contributor III
Contributor III
Author

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-

MonthYearActualPlannedAct/Planned Percentage
Jan 20183210(Actual = 32) * 100 / sum (planned for the year = 320) = 10%

Feb 2018

16255%
Mar 201825257%(say)
Apr 20182525 
May 20183030 
Jun 20184040 
Jul 20182530 
Aug 20183035 
Sep 20182025 
Oct 20182020 
Nov 20182530 
Dec 20181015 
Jan 20192020 

 

Here denominator is sum of calendar year Planned value

Another Look up table is :

PercentageIncrement 
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
Master III
Master III

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
Contributor III
Contributor III
Author

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
Creator II
Creator II

I suppose you want something like the attached. 

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

Ta

Shilpa16
Contributor III
Contributor III
Author

Hi Chirag,

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