topic Re: How to do cumulative sum for two dimension in QlikView

How to do cumulative sum for two dimension

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I would like to ventilate this calculation "=sum({$<FlagOuvertFerme={'Ouverture'}>}CompteurTicket)- sum({$<FlagOuvertFerme={'Fermeture'}>}CompteurTicket)" on two dimensions "month- year" and "status"

When i use on dimension "month-year" and full accumulation it's work

but when i inserted dimension of status, it's not good, numbers are different

Can you help me?

Thank you

Re: How to do cumulative sum for two dimension

sunny_talwar — Tue, 17 May 2016 16:20:47 GMT

May be try this with no accumulation selected:

=RangeSum(Above(TOTAL Sum({$<FlagOuvertFerme={'Ouverture'}>}CompteurTicket)- sum({$<FlagOuvertFerme={'Fermeture'}>}CompteurTicket), 0, RowNo(TOTAL)))

Re: How to do cumulative sum for two dimension

Anonymous — Wed, 18 May 2016 06:32:55 GMT

thank you for your help but it doesn't work figures are wrong whitout accumulation selected

Re: How to do cumulative sum for two dimension

sunny_talwar — Wed, 18 May 2016 08:10:53 GMT

Would you be able to provide a sample to look at?

Re: How to do cumulative sum for two dimension

Anonymous — Wed, 18 May 2016 12:26:39 GMT

Here is the file

thank you very much

Re: How to do cumulative sum for two dimension

sunny_talwar — Thu, 19 May 2016 03:20:51 GMT

I think your issue is that some of the statut are missing dates.

If those were 0 or null, it could have a still worked. The issue is that they are missing. Read here for difference between missing value and null: NULL handling in QlikView

Once you fix this issue, your chart should be fixed.

Best,

Sunny

Re: How to do cumulative sum for two dimension

Anonymous — Fri, 20 May 2016 13:36:00 GMT

Hi,

You were right, I did a test on an Exel file by creating null data on the missing status and it worked. So, I have to create null data in the model it will be difficile. Anyway thank you for your help

Best,

Boubker

Re: How to do cumulative sum for two dimension

Anonymous — Wed, 25 May 2016 07:45:44 GMT

Hi,

Just to tell you I finalize your solution and it works thank you again for your help

best Boubker

Re: How to do cumulative sum for two dimension

sunny_talwar — Wed, 25 May 2016 08:24:03 GMT

Awesome

I am glad you finally made it to work.

Best,

Sunny

Re: How to do cumulative sum for two dimension

rprios90 — Wed, 27 Dec 2017 14:52:10 GMT

Hello,
A cordial greeting,

I'm experiencing the same problem, do you think you can share the solution?

Re: How to do cumulative sum for two dimension

Anonymous — Tue, 02 Jan 2018 09:28:52 GMT

Hello,

In fact, the problem was the lack of data on every month. I had to force data to zero on the missing months on the script. Like this.

my source table is "CONCAT_OUVERT_FERME"

and my axes are : Statut', 'Source_Code

Left join(CONCAT_OUVERT_FERME)

load

[id requesttypes] as 'Source_Code',

[Source de la demande] as 'Source_Lib'

Resident [Type de requete];

wrk_statut0:

load Distinct

Statut as S_L

resident CONCAT_OUVERT_FERME ;

wrk_statut1:

load Distinct

S_L,

'0' as [Additional Field]

Resident wrk_statut0;

wrk_date0 :

load Distinct

Date as d,

MakeDate(year(Date),month(Date),1) as Date1

resident CONCAT_OUVERT_FERME ;

wrk_date1 :

load Distinct

Date1

resident wrk_date0 ;

wrk_date2 :

load Distinct

Date1,

'0' as [Additional Field]

resident wrk_date1 ;

wrk_source0 :

load Distinct

Source_Code as S_C

resident CONCAT_OUVERT_FERME ;

wrk_source1 :

load Distinct

S_C,

'0' as [Additional Field]

resident wrk_source0 ;

Left join (wrk_source1)

load *

Resident wrk_date2;

Left join (wrk_source1)

load *

Resident wrk_statut1;

Left join (wrk_source1)

load

[id requesttypes] as S_C,

[Source de la demande] as Source_Lib

Resident [Type de requete];

CONCAT_OUVERT_NUL:

Load

'0' as 'id tickets',

'0' as 'CompteurTicket',

date(Date1,'DD/MM/YYYY') as Date,

year(Date1)&subfield(date(Date1,'DD/MM/YYYY'),'/',2) as MonthNum,

year(Date1) as Année,

month(Date1) as Mois,

day(Date1) as Jour,

year(Date1)&'-'&month(Date1) as MonthYear,

S_L as 'Statut',

S_C as 'Source_Code',

'Ouverture' as 'FlagOuvertFerme',

Source_Lib

resident wrk_source1;

drop Tables wrk_date0,wrk_date1,wrk_date2,wrk_statut0,wrk_statut1, wrk_source0,wrk_source1;

I hope it will help you

Regards,

Re: How to do cumulative sum for two dimension

Anonymous — Tue, 02 Jan 2018 17:47:42 GMT

This information is important to take into account for future solutions, quite common these drawbacks.

Thanks for the solutions

Re: How to do cumulative sum for two dimension

venkatesan_e — Tue, 05 Feb 2019 16:12:44 GMT

Hi Sunny,

I have problem with cumulative sum for multiple dimension,

YearMonth,SKU group,Sku and etc.

for 1st month same value, when coming to 2nd month cumulative no previous month skugroup and SKU.

Yearmonth, Counts, Cumulative

01 10000, 10000

02 5000,15000

03 2000,17000

Cumulative will happen based on skugroup, SKU - month on Month.

if you have time can you help in this in script level