Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
Nikhil2725
Creator II
Creator II

Table

Hi team,

I need this kind of output..

 Current SchemeNew Scheme  
Sl NumBonus %Current Incremental
Bonus %
New  
73.25%227,5000.50%35,000 H4
83.50%280,00011.00%145,000 H5
93.75%337,50011.00%255,000 H6
104.00%400,00012.50%380,000 H7
114.25%467,50012.50%505,000 H8
124.50%540,00012.50%630,000 H9
135.00%650,00012.50%755,000 H10

 

However, We need to calculate the Current and New column.

Formula for Current is = Sl num*Bonus*1000000

Formula for New is = Sl num*IncrementalBonus %*1000000

Formula for New is = Sl num*IncrementalBonus %*1000000+H4

Formula for New is = Sl num*IncrementalBonus %*1000000+H5

Formula for New is = Sl num*IncrementalBonus %*1000000+H6

Formula for New is = Sl num*IncrementalBonus %*1000000+H7

Formula for New is = Sl num*IncrementalBonus %*1000000+H8

Formula for New is = Sl num*IncrementalBonus %*1000000+H9

 

Kindly Help me in this

7 Replies
Dmk
Contributor II
Contributor II

Can you attach any file? 

Nikhil2725
Creator II
Creator II
Author

Which File should I attach?

Nikhil2725
Creator II
Creator II
Author

This is the Inline table I have where we give the data for every column expect current and new column.

 For the current and new we need to calculate by the following formulas:

For the Current Column formula is straight forward for all the :

Sl_num*Bonus*1000000.

For the New column formula is as follows:

For the first row:

Sl_num*incrematalBOnus*1000000

For the second row:

Sl_num*incrematalBOnus*1000000+Answer of the first row

For the third row:

Sl_num*incrematalBOnus*1000000+Answer of the second row....

Kindly help me in this..........

Prashant_Naik
Partner - Creator II
Partner - Creator II

Hi,

this might help you,

for the first column the formula is straight forward

([Sl Num]*Num(Num#([Incremental Bonus %],'##.##'))*1000000) as Column

For new column calculation you can use the following script in your load script(orange highlight script)

Main:
Load * inline [
Sl Num,Bonus %, Current,Incremental Bonus %,New,dim
7, 3.25, 227500,0.50,35000,H4
8, 3.50, 280000,11.00,145000,H5
9, 3.75, 337500,11.00,255000,H6
10, 4.00, 400000,12.50,380000,H7
11, 4.25, 467500,12.50,505000,H8
12, 4.50, 540000,12.50,630000,H9
13, 5.00, 650000,12.50,755000,H10
];

Final:
Load *,
Text(([Sl Num]*Num(Num#([Incremental Bonus %],'##.##'))*1000000)&if(not IsNull(Previous(dim)),'+')&Previous(dim)) as new_Column

Resident Main;

Regards,

Prashant

 

Nikhil2725
Creator II
Creator II
Author

Hi Prashanth,

Thanks for the reply..

But the Dim column which you have speciied in the Inline that will be not loaded in the script..

Just for the Reference I have mentioned in that..

Channa
Specialist III
Specialist III

New= (Sl num*IncrementalBonus *1000000)+ (Previous(Sl num)*Previous(IncrementalBonus )*1000000)

 

try this

Channa
Nikhil2725
Creator II
Creator II
Author

Hi Channa,

Thanks for the reply..

But Im not getting the desired output..

Output which I'm getting after your suggestion...

Sl_numBonus%Kickback Amt, Current schemeIncremental_Bonus%New_valueDesired_value
73.252275000.50-35000
83.5028000011.00885000145000
93.7533750011.001100000255000
104.0040000012.501360000380000
114.2546750012.501500000505000
124.5054000012.501625000630000
135.0065000012.501750000755000

 

The desired value in table which i'm expecting as the output..

Help me in this