topic 2 values for one field in QlikView

2 values for one field

Mon, 28 Mar 2011 09:01:32 GMT

Hi,

I am using an if statement to show the total of bill amounts per month and at the top of the report there is a total for the year.

The problem I've come across is that if there are 2 amounts for one month. I would usually want to use the 2nd amount.

Using distinct fixes the problem but other accounts will have an incorrect total value because distinct will only take one instance of an amount.

Eg. May 2010 - 15
June 2010 - 15. distinct will only let me take one of those amounts.

The only way around this is if I was to write an if statement to somehow identify if there are 2 of the same months and to take the data in relation to the 2nd instance of the month.

eg. Jan 2011 -20
Jan 2011 -25
my if statement would take the 2nd one.

my code is sum(distinct bill_amount).

if anyone can shed some light on how I would write this if statement or is it possible to use set anaylsis for this type of issue?

Any help is much appreciated,
thanks in advance

2 values for one field

Mon, 28 Mar 2011 09:18:57 GMT

How are you going to decide which one is the second one? By the order?

2 values for one field

Mon, 28 Mar 2011 09:24:02 GMT

Hi Iassen,

yeah the second one by order

2 values for one field

Mon, 28 Mar 2011 09:36:56 GMT

OK, can you now share a sample of your report, so that I can give you the correct formula?

2 values for one field

Mon, 28 Mar 2011 10:16:10 GMT

Iassen, I've tried to attach a sample but it won't let me.

All I was going to send was a qvw that used a pivot table. The table contained 2 dimensions - Account and Month. The expression was sum(distinct amount) as Total.

This data was loaded in from a spreadsheet.

The table looked like this:
Account Month Total
Total 2064
12345 Jan 1000
12345 jan 1064

Does this help any?

2 values for one field

Wed, 30 Mar 2011 08:18:03 GMT

I will need to see your raw data as well. Can you paste some of it here as well?

2 values for one field

Wed, 30 Mar 2011 08:29:48 GMT

If it is always the highest number in each month you need, you might be able to do the following:

Sum(aggr(max(distinct bill_amount), month))

Although this would not work if you need to take account of credits.