topic Re: Cumulative Sum in stacked Bar Chart in QlikSense in App Development

Cumulative Sum in stacked Bar Chart in QlikSense

jazzsran — Fri, 15 Nov 2024 21:40:17 GMT

Hi,

I have stacked barchart, with two dimensions country and YearMonth and a measure sum(Sales) but for each country the source for sales is different, also the field names are different. suppose sales1 and sales2

I am using if statement on country to guide to the correct calculative statement which is working fine.

if(country='J',sum(sales1),if(country='I',Sum(Sales2)))

Now, there is a requirement that one of sales is already cumulative other should be calculated in the chart.

When I apply this formula only on that country it works fine, but when I add it in the If statement related data just disappeared and country from the chart.

if(country='J',sum(sales1),if(country='I',Aggr(RangeSum(Above(Sum(sales2), 0, RowNo())),YearMonth)))

Could anyone please guide me?

Thanks

Re: Cumulative Sum in stacked Bar Chart in QlikSense

rubenmarin — Sun, 18 Jun 2023 06:56:47 GMT

Hi, I'm not sure but maybe adding country to the aggr():

Aggr(RangeSum(Above(Sum(sales2), 0, RowNo())),YearMonth,Country)

Re: Cumulative Sum in stacked Bar Chart in QlikSense

jazzsran — Mon, 19 Jun 2023 09:36:02 GMT

Hi,

No, it doesn't work as country is one of the dimension for the bar chart.

Issue is if I select any country the bar chart selects it and it works fine.

but with the expression in the measure for the bar chart:

if(country='J',sum(sales1),if(country='I',Aggr(RangeSum(Above(Sum(sales2), 0, RowNo())),YearMonth)))

The country 'I' disappears rest is shown. However still with this if I select 'I' from filters, the data appears in the same bar chart. very strange...

normally:

when filter on 'I', only "I" appears:

is it possible Rangesum or Aggr doesn't work in If statements?

I am thinking of calculating the cumulative sum in the script for 'I' as well, others are already cumulative.

Thanks,

Re: Cumulative Sum in stacked Bar Chart in QlikSense

barnabyd — Mon, 19 Jun 2023 21:58:48 GMT

G'day @jazzsran,

I would bypass the problem by creating a new column in the load script according to your formula ...

rangesum( sales1, Sales2, ... ) as Sales

Then the expression in the chart becomes a normal aggregation.

I hope this helps. Cheers, Barnaby.

Re: Cumulative Sum in stacked Bar Chart in QlikSense

jazzsran — Mon, 31 Jul 2023 17:55:18 GMT

Hi,

Sorry for late reply.

@barnabyd , while your solution might work, I actually created a cumulated sum for another Sales as well and displayed on the chart. It worked fine.

Thanks