Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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