topic savings 2015,2016,2017 calculation problem in QlikView

savings 2015,2016,2017 calculation problem

Anonymous — Fri, 03 Jul 2015 09:42:48 GMT

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 — Fri, 03 Jul 2015 10:05:18 GMT

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 — Fri, 03 Jul 2015 10:22:21 GMT

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 — Sat, 04 Jul 2015 07:13:11 GMT

jagan and settu_periasamy,

Good solution. Thank you.