topic Re: Add individual months for total in QlikView

Add individual months for total

pauldamen — Thu, 31 Jul 2014 07:33:44 GMT

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

Re: Add individual months for total

crusader_ — Thu, 31 Jul 2014 07:43:08 GMT

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

Re: Add individual months for total

pauldamen — Thu, 31 Jul 2014 07:58:25 GMT

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

Re: Add individual months for total

sujeetsingh — Thu, 31 Jul 2014 08:15:44 GMT

May this help you

Re: Add individual months for total

pauldamen — Thu, 31 Jul 2014 08:29:34 GMT

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

Re: Add individual months for total

sujeetsingh — Thu, 31 Jul 2014 08:58:46 GMT

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.

Re: Add individual months for total

simenkg — Thu, 31 Jul 2014 09:04:43 GMT

Try using an expression with the Aggr() function.

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

Re: Add individual months for total

Thu, 31 Jul 2014 09:14:34 GMT

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