Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

selcukcadir
Contributor

rolling last year total

Hi,

gwassenaar published  (thanks you gwassenaar)


I want to add

last year total sales

add as a new column

This can be done added ?exp5.png

1 Solution

Accepted Solutions

Re: rolling last year total

Add records to the AsOf table to link each period with every month of the previous year:

CONCATENATE (AsOfPeriodTable)

load Period as AsOfPeriod

,'LastYearTotal' as PeriodType

,date(addmonths(yearstart(Period),-iterno()),'YYYYMM') as Period1

,Year(Period) as Year

Resident PeriodTbl

while IterNo()<=12;

Then use this expression: sum({<PeriodType={'LastYearTotal'}>}Sales)


talk is cheap, supply exceeds demand
5 Replies
selcukcadir
Contributor

Re: rolling last year total

Hi all,

Could you give support to this issue?

thanks.

selcukcadir
Contributor

Re: rolling last year total

Hi all ,

set analysis ,How to solve this problem?

selcukcadir
Contributor

Re: rolling last year total

hi all,

correct  ;

if(Year(AsOfPeriod)-1 = '2009',sum(total {$<Year={$(=2009)},PeriodType={'Current'}>} Sales))     

But not correct ; dynamic solution

1 trial ;    sum(total {$<Year={"=$(#=Year(=Only(AsOfPeriod)-1))"},PeriodType={'Current'}>} Sales)

2 trial ;    sum(total {$<Year = {"$(Year(AsOfPeriod)-1)"},PeriodType={'Current'}>} Sales)

vLast  = Year(AsOfPeriod)-1

3 Trial ; sum(total {$<Year={$(vLast)},PeriodType={'Current'}>} Sales)

Why not correct  ? Help....

Re: rolling last year total

Add records to the AsOf table to link each period with every month of the previous year:

CONCATENATE (AsOfPeriodTable)

load Period as AsOfPeriod

,'LastYearTotal' as PeriodType

,date(addmonths(yearstart(Period),-iterno()),'YYYYMM') as Period1

,Year(Period) as Year

Resident PeriodTbl

while IterNo()<=12;

Then use this expression: sum({<PeriodType={'LastYearTotal'}>}Sales)


talk is cheap, supply exceeds demand
selcukcadir
Contributor

Re: rolling last year total

Thank you Gysbert,

I have another question. Can you help me.

Period Year Ago  and  YTD Year Ago    how to add ?

thank you for great post

Community Browser