topic Re: Calculate Expression by Subtraction in QlikView

Calculate Expression by Subtraction

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a data set that provides balances at month end for each month. Example of Data:

Month Sum(Balance)

201712 21 500

201801 22 900

201802 23 700

I would like to show a bar chart showing the YTD growth in balance for each month from 201712 - for example

201801 = 600 (Sum of 201801 Balance) - (Sum of 201712 Balance)

201802 = 2 200 (Sum of 201802 Balance) - (Sum of 201712 Balance)

Month is loaded as the dimension.

I have tried the following expressions:

(Sum(Balance))-(Sum({<Month={'201712'}>}Balance))

This results in the following results in the bar graph:

201712 = 0

201801 = 22 900

201802 = 23700

(Sum(Balance))-(Sum({<Month={'201712'}>}Total<Month>Balance))

Same Result as Previous one

[Sum(Balance)]-[Sum({<Month={'201712'}>}Balance)]

This Displays No Data

I have also tried to create a separate expression as follows:

FY2017 = Sum({<Month='201712'>}Balance)

I then tried the following expression:

Sum(Balance) - FY2017

This also gives me the following result:

201712 = 0

201801 = 22 900

201802 = 23700

Any ideas?

Thanks

Re: Calculate Expression by Subtraction

Lisa_P — Tue, 27 Mar 2018 04:20:01 GMT

This is the expression you need:

Sum(Balance)-Sum({<Month={201712}>}TOTAL Balance)

Re: Calculate Expression by Subtraction

shiveshsingh — Tue, 27 Mar 2018 06:33:19 GMT

Try this expression

sum(Balance)-sum(TOTAL{<Month = {'201712'}>}Balance)

Re: Calculate Expression by Subtraction

sasiparupudi1 — Tue, 27 Mar 2018 09:37:16 GMT

May be try

(Sum(Balance))-(Sum({1<Month={'201712'}>}Balance))

(Sum(Balance))-(Aggr(Sum({1<Month={'201712'}>}Balance),YourDimension))

Re: Calculate Expression by Subtraction

Anonymous — Wed, 28 Mar 2018 08:48:49 GMT

Thank you!! - This worked.

Re: Calculate Expression by Subtraction

Anonymous — Wed, 28 Mar 2018 08:49:09 GMT

Thank you - This Worked!