Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
luohda
Contributor III
Contributor III

Cumulative sum in bar chart with 2nd dimension which does not exist in every 1st dimension

I want to do a cumulative sum of each Probe, grouped in 2 dimensions: accounts and Yearmonth.

but, in some months there're no probes from certain accounts, meaning, this account does not exist if I select specific yearmonth. That's why in cumulative chart, some months are lacking the values of some accounts.

kontierung.png

first graph is normal sum of each month:

sum(aggr([PREIS]*(1-[FIRM_RABATT]/100)*num([ANZAHL])*FACTOR, [Probe_Nr]))

the second chart is cumulative sum:

aggr(RangeSum(Above(total
 sum(aggr([PREIS]*(1-[FIRM_RABATT]/100)*num([ANZAHL])*FACTOR, [Probe_Nr])),
    0, RowNo())) ,ACCOUNT, (YEARMONTH,(numeric, ascending)))

As you can see in the second graph, in July and December, account ALM BODENLUFT is missing. How to make the chart show all 2nd dimensions?

Labels (1)
1 Solution

Accepted Solutions
luohda
Contributor III
Contributor III
Author

thanks for the quik reply, sadly no, it doesn't work. Because the account is missing in December and July, you've commented in other question that if the value is not null but missing, then it would need to be fixed in data loading. Does it mean that the only solution is to add an 0 value of the missing account in these 2 months in data loading?

View solution in original post

5 Replies
sunny_talwar

Not sure if this will work or not based on your data... but try this

Aggr(
  RangeSum(Above(TOTAL
    Sum(Aggr([PREIS]*(1-[FIRM_RABATT]/100)*num([ANZAHL])*FACTOR, [Probe_Nr]))
    +
    Sum({1} 0)
  , 0, RowNo()))
, ACCOUNT, (YEARMONTH, (numeric, ascending)))
puneetagarwal
Partner - Creator II
Partner - Creator II

Hi Luohda,

May be go through the below thread
https://community.qlik.com/t5/New-to-Qlik-Sense/cumulative-sum-in-bar-chart/td-p/33644/page/2


In your expression please add both the dimension
aggr(RangeSum(Above(total
 sum(aggr([PREIS]*(1-[FIRM_RABATT]/100)*num([ANZAHL])*FACTOR, [Probe_Nr])),
    0, RowNo())) ,ACCOUNT, (YEARMONTH,(numeric, ascending)))

I believe Account is your 1st dimension and 2nd dimension is  missing i believe please check and add accordingly it missing.

Another reason could be the data is missing for those 2 months in data source please verify.

Regards,
Puneet Agarwal

luohda
Contributor III
Contributor III
Author

thanks for the reply,
(YEARMONTH,(numeric, ascending)) is the first dimension, ACCOUNT is the second. So far everything works, just the missing value for that missing acount in 2 months.
yes the account dimension is missing in those 2 months, how can I make it pop out? even when it does not exist in these 2 months?
luohda
Contributor III
Contributor III
Author

thanks for the quik reply, sadly no, it doesn't work. Because the account is missing in December and July, you've commented in other question that if the value is not null but missing, then it would need to be fixed in data loading. Does it mean that the only solution is to add an 0 value of the missing account in these 2 months in data loading?
sunny_talwar

Yes, you are right...