Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
mandyaryel
Contributor III
Contributor III

Sum Different columns on a dinamic Table

Hi all,

I need to create a column using the following idea:

sum all values on the column 'Valor Realizado' to the year 2013 and divide with the sum all values on the column 'Valor Realizado' to the year 2014

How can I do it? have any Idea.

I tried do use sum(column()) But this is a Dinamic table with so many dimensions.

Hope you can help.

Awaiting.

Thanks!

Amanda Aryel

1 Solution

Accepted Solutions
reddy-s
Master II
Master II

Hi Amanda,

Do you have a master calendar created in your data model. If so it will be quite straight forward to get this calculation done.

use: sum({<CalYear={"=$(year(today()))"}>} Sum_field) /  sum({<CalYear={"=$(year(addyears(today(),-1)))"}>} Sum_field)

or if you have flags set for this year and last year

use: sum({<[this Year flag]={1}>} sum_field) / sum({<[Last Year flag]={1}>} sum_field)

To set the flags in the master calendar make use of inYear() function.

Thanks,

Sangram.

View solution in original post

7 Replies
sinanozdemir
Specialist III
Specialist III

Hi,

You can try something like this:

Sum({<Your_Date_Field={">=01/01/2013<=12/31/2013"}>} Total Your_Field) / Sum({<Your_Date_Field={">=01/01/2014<=12/31/2014"}>} Total Your_Field)

Since I don't know your fields and date formats, the above should give you some pointers.

You can also post some sample data.

Hope this helps.

mandyaryel
Contributor III
Contributor III
Author

Thanks for reply,

It helps in part,

but using this expression, it show the same value (a total) in each row.

and I need that every row also show the result, just like the column '% Acumulado Total'.

do you have any idea if is possible?

sinanozdemir
Specialist III
Specialist III

Well, that's what Total does. In your original question, you said "sum all values on the column" so Total sums all the values.

Would you happen to have a sample dataset so that we can take a look at it closer?

Thanks

mandyaryel
Contributor III
Contributor III
Author

Yeh, I Got it.

Let me try improve my description.

What I need it is:

I need add one more Column '% - Previous Year/ Present Year'

where the calculation is:

'Valor realizado' on the year 2013 divided 'Valor Realizado' on the year 2014.

I don't wanna all values like total does. I need something like the first picture above.

Its the same way that my column '% Acumulado Atual'.

Is it possible?

THANKS

Obs.:

That's why I tried to use the function column().

sinanozdemir
Specialist III
Specialist III

How about this:

Capture1.PNG

reddy-s
Master II
Master II

Hi Amanda,

Do you have a master calendar created in your data model. If so it will be quite straight forward to get this calculation done.

use: sum({<CalYear={"=$(year(today()))"}>} Sum_field) /  sum({<CalYear={"=$(year(addyears(today(),-1)))"}>} Sum_field)

or if you have flags set for this year and last year

use: sum({<[this Year flag]={1}>} sum_field) / sum({<[Last Year flag]={1}>} sum_field)

To set the flags in the master calendar make use of inYear() function.

Thanks,

Sangram.

mandyaryel
Contributor III
Contributor III
Author

Thx , it helps me a lot!