Count down records in a Table

[#1]

Hi Guys,

I have created the following table in Qlikview

CustomerName          StartDate          EndDate          Guarantee               Savings                    BalanceSavings

                                                                                € 6,958.73               € 1,298.48               € 5,660.25

Data Space               1/2/2015            1/2/2017          € 6,958.73                € 0.00                     € 6,958.73

Data Space               1/3/2015            1/2/2017          € 6,958.73                € 419.62                  € 6,539.11

Data Space               1/4/2015            1/2/2017          € 6,958.73                € 331.62                  € 6,627.11

Data Space               1/5/2015            1/2/2017          € 6,958.73                € 264.62                  € 6,694.11

Data Space               1/6/2015            1/2/2017          € 6,958.73                € 282.62                  € 6,676.11

I made the following calculations:

Guarantee : Sum(Distinct(Guarantee)

Savings: Sum(Savings)

BalanceSavings: Sum(Distinct(Guarantee@@@ - Savings).

The totals on the first row gave what  I expected. But as you can see on the 1/6/2015, I am still having a BalanceSavings of € 6,676.11 while the total on the first row as of that date shows € 5,660.25. I know the expression is taken into consideration the Guarantee - Savings as the Months increased. But I will like to have it as the BalanceSavings - Savings as the months increased. So as the Total on the first row shows € 5,660.25, on the 1/6/2015 I will like to see that same value as well so its logical.

I tried the following expression:

If(RowNo() = 1, Sum(Guarantee - Savings), Balance - (Savings))

Here Balance is the expression Label for the expression. But still it did not work. Please any help to achieve my desired result will be appreciated.

Thanks,

Simon

[swuehl]

Try

=If(Dimensionality() =0 , "Guarantee" - "Savings",

Sum(Distinct(Guarantee)) - Rangesum( Above(Sum(Savings),0,rowno() ))

)

The Sum(Distinct(Guarantee)) part looks a bit strange though, have you joined the guarantee value to each date record?

[#3]

Hi Swuehl,

I did use the expression you sent me and it worked. Thanks very much for your help. You have help me out a couple of times with answers to questions I posed in the community and I really do appreciate that.

Please I am new to qlikview and I am just getting to understand the tool more and its amazing capabilities in data analytics. Please if you don't mind maybe you can send me an email address that I can use to send you any more questions I will have in the future.

Once again thanks for your help and I am looking forward to receiving more of such help from you  in the future.

Kind Regards,Simon