Count down records in a Table

Fri, 20 Nov 2020 18:02:30 GMT

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

Re: Count down records in a Table

swuehl — Wed, 02 Dec 2015 20:37:22 GMT

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?

Re: Count down records in a Table

Thu, 03 Dec 2015 10:13:52 GMT

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

topic Count down records in a Table in QlikView

Count down records in a Table

Re: Count down records in a Table

Re: Count down records in a Table