topic Re: Adding a value based on RowNo in QlikView

Adding a value based on RowNo

Thu, 13 Oct 2011 15:57:35 GMT

Hi,

I have a pivot table:

The expression for column 1 is:

If(RR2_RowNo=30, Sum(RR_Belopp), Sum(RR_Verksamhetsmål))/1000

But now the customer wants to add the value from column 1, RR2_RowNo 30 (-49 937) to RR2_RowNo10 (270 771+49 937).

Is that possible?

I've tried with:

If(RR2_RowNo=30, Sum(RR_Belopp), If(RR2_RowNo=10, (Sum(RR_Verksamhetsmål)+Sum({$<RR1_RowNo={30}>} RR_Belopp)), RR_Verksamhetsmål))/1000

But that din't help...

Some ideas?

@Ungvall

Senior Business Discovery Manager at Advectas AB

Re: Adding a value based on RowNo

Miguel_Angel_Baeyens — Thu, 13 Oct 2011 16:27:12 GMT

Hi Ungvall,

I may be missing something but check the attached application to see whether that is waht you are looking for. Basically, I'm using Dimensionality() function to display the "normal" Sum() for each dimension but show depending on variables the "Total" line.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Adding a value based on RowNo

Thu, 13 Oct 2011 16:38:57 GMT

Hi,

Didn't get that... 😉

But basically:

Expression for column 1:

If(RR2_RowNo=30, Sum(RR_Belopp), Sum(RR_Verksamhetsmål))/1000

Table

RowNo Dim Column 1

10 A 10+30

20 B 20

30 C 30

When calculating the expression counts for everything except RowNo 10. They want to add the value from RowNo 30 when calculating RowNo 10...

Does that make sense?

@Ungvall

Senior Business Discovery Manager at Advectas AB

Re: Adding a value based on RowNo

Miguel_Angel_Baeyens — Thu, 13 Oct 2011 16:50:08 GMT

Hi,

Perhaps something like this instead in the expression?

Sum(If(RowNo = 10, $(=Sum({< RowNo = {$(vRows)} >} Amount)), Amount))

Hope that helps,

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Adding a value based on RowNo

dpietersz — Fri, 14 Oct 2011 06:43:51 GMT

I'm not sure if I understand you correctly, but this is something I use often with Financial Reporting. Maybe it helps.

Re: Adding a value based on RowNo

Fri, 14 Oct 2011 06:54:36 GMT

Hi,

Thank you, but I'm already using a similar template and adding a row for RowNo 10 would be the easiest way. But the problem is that RowNo 30 doesn't have any data. That's way I have to use...

If(RR2_RowNo=30,Sum(RR_Belopp), Sum(RR_Verksamhetsmål))/1000

...to capture the value from Total. Problem comes when I have to add the value from RowNo 3 to RowNo 1 when I already have exceptions in the expression.

@Ungvall

Senior Business Discovery Manager at Advectas AB

Re: Adding a value based on RowNo

Sokkorn — Fri, 14 Oct 2011 07:07:04 GMT

Hi Ungvall,

Try this =SUM(If(RR2_RowNo=30,RR_Belopp, RR_Verksamhetsmål))/1000

Regards,

Sokkorn

Re: Adding a value based on RowNo

Fri, 14 Oct 2011 07:12:13 GMT

Hi,

That's a way of what I'm already using. I need to add the value from Row 30 to Row 10 in the expression. Something like:

If(RR2_RowNo=30, Sum(RR_Belopp), If(RR2_RowNo=10, (Sum(RR_Verksamhetsmål)+Sum({$<RR2_RowNo={30}>} RR_Belopp)), RR_Verksamhetsmål))/1000

But that only returns value on RowNo 10 and 30, and not with tha value from 30 added to 10...

@Ungvall

Senior Business Discovery Manager at Advectas AB

Re: Adding a value based on RowNo

dpietersz — Fri, 14 Oct 2011 07:17:57 GMT

Could you make a simple QlikView File with what you have up to this point. Including a datamodel?

I think you are close. But it's hard for me without an example.

Adding a value based on RowNo

Fri, 14 Oct 2011 07:42:24 GMT

Hi,

See if you can open the attached file above...

@Ungvall

Senior Business Discovery Manager at Advectas AB

Re: Adding a value based on RowNo

llauses243 — Fri, 14 Oct 2011 09:30:12 GMT

Hi,

This is my offer, pls to see image attached

Good luck, Luis

Adding a value based on RowNo

Fri, 14 Oct 2011 11:39:15 GMT

Hi,

Thanks - but the result should be:

RowNo 10 424 221

RowNo 20 -38 822

RowNo 30 -18 065

Based on your pict.

@Ungvall

Senior Business Discovery Manager at Advectas AB

Adding a value based on RowNo

rbecher — Fri, 14 Oct 2011 13:54:42 GMT

Basically we are doing such things on the data level by joining a table with the aggregation logic.

- Ralf

Re: Adding a value based on RowNo

s_uhlig — Fri, 14 Oct 2011 16:31:34 GMT

Hi,

May be you miss to total your sum

If(RR2_RowNo=30,  
     Sum(RR_Belopp) , 
     If(RR2_RowNo=10,
          Sum(RR_Verksamhetsmål)+Sum({<RR2_RowNo={30}>} total RR_Belopp),
          Sum(RR_Verksamhetsmål) 
     )
)/1000

Regards

Sven

Re: Adding a value based on RowNo

llauses243 — Sat, 15 Oct 2011 00:06:11 GMT

Hi,

This is my offer, pls to see image attached

Good luck, Luis