topic Cumulative sum with set analysis in QlikView

Cumulative sum with set analysis

Wed, 15 Dec 2010 17:04:44 GMT

Hi all,

i have a table that contains the following data:

year month Code_mag debit

2010 09 100 100

2009 09 111 200

2010 08 100 300

2009 08 111 400

....

i have a pivot table that calculates the cumul debit .

so when i select for example in my calendar month 09 and Year 2010 ; i have to calculate the cumul sum when month <= selected month

i dont have a problem for 2010 and the expression is:

=sum({< Mois = {'<=$(vMois)'}>} [Debit_Mag.Nombre de Débits Magasin])

vMois =GetselectedField(Mois)

my problem is when i want to calculate the cumul sum of (Year -1)

the expression (SUM( { $<Année={$(=Only(Année)-1) } > {< Mois = {'<=$(vMois)'}>} [Debit_Mag.Nombre de Débits Magasin])) dont work

any ideas ???

thanks,

marcel_olmo — Fri, 17 Dec 2010 03:12:52 GMT

Hey Ichark, how about using above() function?

What I say is, if is the first row, your cumullative is the first sum value, otherwise you have to sum the current value with the above value.

Something like this (untested), please add your set analysis stuff :

if ( rowno() = 1, sum( debits magasin), sum(debits magasin) + above (sum(debits magasin)) )

Hope it helps!

Wed, 21 Dec 2011 09:42:07 GMT

Thanks Marcel. It helped me a lot today.

Piskitta