savings 2015,2016,2017 calculation problem

[Anonymous]

i have

Year, Savings, Cost  avoidance,Current Year Savings   columns.

savings 2015= savings+cost avoidance /left(contract term,1)

savings 2016= savings+cost avoidance /left(contract term,1)

savings 2017= savings+cost avoidance /left(contract term,1)

i want out put like below

Year	Savings 2015	Savings 2016	Savings 2017
2015	16666.66	0	0
2016	0	16666.66	0
2017	0	0	16666.66

please find the sample application !

[settu_periasamy]

Hi,

Try the attachment. You can use the expression like the below

=(sum({<Year={2015}>}Savings)+sum({<Year={2015}>}[Cost Avoidance]))/Left([Contract Term Years],1)

=(sum({<Year={2016}>}Savings)+sum({<Year={2016}>}[Cost Avoidance]))/Left([Contract Term Years],1)

=(sum({<Year={2017}>}Savings)+sum({<Year={2017}>}[Cost Avoidance]))/Left([Contract Term Years],1)

[jagan · Accepted solution]

HI,

Calculate this in script itself by using below script

Savings:

LOAD Year,

     [Description Project Event Name],

     Savings,

     [Cost Avoidance],

     [Current Year Savings],

     [Savings %],

     [Project Type],

     [Contract Term Years],

     [Savings 2015],

     [Savings 2016],

     [Savings 2017],

     (Savings + [Cost Avoidance])/Num(SubField([Contract Term Years], ' ', 1)) AS NetSavings    

FROM

(ooxml, embedded labels, table is Sheet1);

Create 3 expressions

2015 Savings =(sum({<Year={2015}>}NetSavings )

2016 Savings =(sum({<Year={2016}>}NetSavings )

2017 Savings =(sum({<Year={2017}>}NetSavings )

Hope this helps you.

Regards,

Jagan.

[qlikviewwizard]

jagan and settu_periasamy,

Good solution. Thank you.