Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

how to add a colum with totals

hi everyone;

i have the next table with mesure acumulate

for month march total acum for 3 months  


total acum 600

Jan  100

Feb   200

Mar   300

for month february total acum for 3 months

total acum 350

dic   50

Jan 100

Feb 200

for month january total acum for 3 months

total Acum 500

Nov 100

Dic    300

Jan   100

how i get a table with the acumulate for this way

over the total


Jan   500

Feb   350

Mar  600

8 Replies
MVP
MVP

Re: how to add a colum with totals

Use RangeSum() along with Above() :

2015-03-18 #2.PNG

MVP
MVP

Re: how to add a colum with totals

under expression

Accumulation--> Accumulate --> 3 steps back

Not applicable

Re: how to add a colum with totals

Hi Petter thanks for your response but doesn't work because when i have select a dimention year if you seen the acumulate it make it this way:

March = March+February +January

Feb = February +January+December

January= January+December+November

December = December +November

I make the test with this in my application and doesn´t work i don´t know what´s going on

the acumulate is make this way: if i select YEAR=2015 thae acumulate :

March = March+February +January

Ferburary=February +January

Janury = January

I neet this way

Acumulados.jpg

hariprasadqv
Contributor III

Re: how to add a colum with totals

Hi,

if you are doing it in UI side use Accumalation. If it is expression/script go for Rangesum with Above or Below based on your requirement.

MVP
MVP

Re: how to add a colum with totals

To achieve what you want you will have to have an additional field which is composed of Year and Month like this:

           Year(Date) & '-' & Month(Date) AS YearMonth

then YearMonth will be the field you use in your expression as this will give you consequtive months which will traverse years... The Above() function will then be able to find previous (Above) months even in January and Febuary

Not applicable

Re: how to add a colum with totals

Hi,

Try like this,

Dimension:

                any one field name (Month_Name)

Expression:

              Sum(First Table Value)+Sum(Second Table Value)+Sum(Third Table Value)

Gabriel
Valued Contributor II

Re: how to add a colum with totals

Hi,

I will look to bring the accumulated field in from the script. Reason is you can take advantage of PEEK() function.

Example

Final_Table:

LOAD

A,B

IF(Month = Peek(Month),RangeSum(FigureFieldName,Peek(AccumulatedField)),FigureFieldName)   AS AccumulatedField

RESIDENT Temp_Table

And Order By A,B;

Drop table FigureFieldName;

Hope this helps

Not applicable

Re: how to add a colum with totals

Petter really and also people thanks for your help

i did what you said petter and i added a new dimention with the Year and month in this way 201501inside of the table and just work it when i don't select any dimention in

year or month what i mean is that when i select year 2015 and current month is cut te measure. i try to put this in my first expression like a set analysis sum({$<Year=>} measure)

after my second expression rangesum(Above(test,0,3))

Community Browser