topic Re: Change sum value in QlikView

Change sum value

Fri, 18 May 2012 18:26:55 GMT

Hi guys,

I'm new in this forum and didn't find any answers about my problem.

My pivot table has an expression (Valor_total) that sum 3 diferent variables (VALOR_1, VALOR_2, VALOR_3). I have to modify manually some of these sum's(not each variable), there is no specific rules. What can I do to input this value in the sum expression?

Tks,
Leonardo

Change sum value

Fri, 18 May 2012 21:09:04 GMT

Can you provide more information? You can declare variables in QV and then set the value in for the variable via an input box. However, the variable value would be applied to all rows in the dataset.

Change sum value

Fri, 18 May 2012 21:16:09 GMT

But I want to change some of this values, not all of them. Eg.

NAME VALOR_TOTAL (V1+V2+V3)

XXX            3000
YYY            2000
ZZZ            1500
AAA            500

I wanna change the sum of AAA to 550 but keep the other values, and I don't wanna change V1,V2 and V3.

Change sum value

Fri, 18 May 2012 21:24:55 GMT

I thought that was what you desired but I wanted to make sure. Are you trying to permanently adjust the value or run what if scenarios? The only thing I can think of off the top of my head is to load the adjustments either via an inline load or via an externally maintained data source (text file or Excel file). With these, you adjustments would be applied everytime you reloaded the QVW.

Change sum value

Fri, 18 May 2012 21:31:31 GMT

is it possible? i don't want to change the original values, only the sum expression. can i make it via inline load? how do I do this?

Re: Change sum value

Fri, 18 May 2012 21:36:37 GMT

Here is the example. The level at which you track the data (i.e. if you wanted to track adjustments for a name and date) can be increased. The real limiting factor is how many rows you need to adjust and the fact that it is a manual process. It might be easier to track the data in Excel so that others could help maintain it and you are not constantly editing the script in the QVW.

Change sum value

Fri, 18 May 2012 21:52:49 GMT

Yeah, almost that. but I want this sum table with all values of the original table, and i dont want this 'incremental adjustment' , but the "full adjustment". i want to type on this "adjustment table" the exact value that will be showed on the Pivot table. Can you get it? Sorry my English, I'm from Brazil, My english is not fully efective. hahahah

Change sum value

Fri, 18 May 2012 21:55:05 GMT

I will modify 3 or 4 rows only. I can do this manually, no problem.

Re: Change sum value

Fri, 18 May 2012 21:56:52 GMT

I don't think I understand? Do you want the adjusted value to be 550? If that is the case, you can just change the value in the inline "Adjustment" table to be 550 and simplify the expression to be sum(Adjustment).

Re: Change sum value

Fri, 18 May 2012 22:03:56 GMT

I re-read your last post. I don't think you want to type the exact value. If you do this, you will have to enter a value for each row, regardless of whether it needs to be adjusted or not. The method I used sums up the existing data and then adds in your adjustment amount. This way, you only need to add data to the inline table for the rows that need to be adjusted. Using this method will also allow you to track the rows you have adjusted.

Change sum value

Fri, 18 May 2012 22:10:24 GMT

I got you point. But all I need is this table you sent me, the Sum Table should be like this:

NAME               SUM
AAA                    550
XXX                     3000

ZZZ 2000
YYY 1500

and table adjustment with

AAA 550

got it?

I'm not able to get this incremental adjustment, I only have the full value

Re: Change sum value

Fri, 18 May 2012 22:13:28 GMT

I think I get it. How about this? I did not rename the tables but there are three now: 1 with the original value; 1 with the original value and the adjusted value; 1 with only the adjusted value. I am heading home for the day. I think you have what you need to continue. You probably just need to experiment with how you want to display the data. The formulas for all the fields are in the Straight Table objects. You can alter the formulas as you see fit. Regards.

Change sum value

Fri, 18 May 2012 22:22:59 GMT

Yeah, this way. Only that should be diferent

Adjustments:

load * inline [
Name, Adjustment
AAA, 50];

should be

Adjustments:

load * inline [
Name, Adjustment
AAA, 550];

And This 550 being a inputbox, and shown on the Sum Table as "550".

Its something like that

AAA = SUM (ADJUSTMENT)
THE REST OF VARIABLES = V1+V2+V3

Got it?

This 550 i'll input manually. This 50 should not exists

Change sum value

Fri, 18 May 2012 22:26:28 GMT

You got it. I'll have to put a conditional sum in order to have it. In this case AAA the sum will be diferent of the others cases. Your query is ok. Thanks a lot for the patience