Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have an application where an amount needs to be calculated per month. This calculation is a Rate * UsageAmount. This works fine for individual months.
What I need is when I select for example 3 months the total shown is month 1 + month 2 + month 3. Now if I select nothing is adds up the rates for different months making a ridicules number. The average of the rate doesn't give a good picture either.
Hope someone can help!
Regards,
Paul
Try using an expression with the Aggr() function.
i.e:
Sum(Aggr(Sum(Rate*UsageAmount),Month,Year))
Hi,
When you selected nothing Qlikview sum() everything and of course it doesn't have any sense.
You can Check "Always checked one value" on your listboxes to avoid unchecking at all.
But if you want to clear all fiters, please describe, what number do you want to see?
Better if you attach qvw sample or pictures with your data and expected results.
HTH.
Andrei
Hi,
Thanks for your reply. The QVW is a mess so it wouldn't help to attach that, but I will try to describe:
A number is calculated per month based on 2 field: Rate, Amount of Customers
Rate Cust Tot
Jan: 5 10 50
Feb: 10 10 100
Mar: 10 8 80
When selecting nothing it should show: 50 + 100 + 80 = 230. When selecting Jan en Feb it should show: 50+100 = 150
So it should regardless the selection, when having multiple months add up the individual months
Regards,
Paul
May this help you
Looks great but what did you exactly do? My full formulas look like this:
Rate:
sum({<Code = {'SAC Costs'}>Rate}
Customers:
sum({<Rateplan = {'Simple'}, DSCode = {'IDS'}>} GrossAds)
How can I implement your solution in my formulas?
Regards,
Paul
Paul,
just go through the expression i have used .
Just study it well and then just replace the Rate with your formula,
and in the same for the others too.
Try using an expression with the Aggr() function.
i.e:
Sum(Aggr(Sum(Rate*UsageAmount),Month,Year))
Hi Paul,
If you have a large data set, it is also worth saying that, if possible you should pre-calculate Rate*Cust in your script and create a new field which you can then do your sum on.
Failing that as Simen says use the aggr function
Joe