Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pauldamen
Partner - Creator II
Partner - Creator II

Add individual months for total

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

1 Solution

Accepted Solutions
simenkg
Specialist
Specialist

Try using an expression with the Aggr() function.

i.e:
Sum(Aggr(Sum(Rate*UsageAmount),Month,Year))

View solution in original post

7 Replies
crusader_
Partner - Specialist
Partner - Specialist

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

pauldamen
Partner - Creator II
Partner - Creator II
Author

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

sujeetsingh
Master III
Master III

May this help you

pauldamen
Partner - Creator II
Partner - Creator II
Author

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

sujeetsingh
Master III
Master III

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.

simenkg
Specialist
Specialist

Try using an expression with the Aggr() function.

i.e:
Sum(Aggr(Sum(Rate*UsageAmount),Month,Year))

Not applicable

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