RangeSum Function with If Condition

[mustafatanyer]

Hi,

So I have this rangeSum function in a table perfectly working:

rangeSum (Below(Sum([Sales]),0,($(vMonthCalc)+1)))

Here variable vMonthCalc keeps track of the interval between current month and the beginning of the year by checking a date field. (I.E. for 01.01.2018 it holds 0, for 01.02.2018 it holds 1, 01.11.2017 it holds 10 and so on).

And I have two products in my Products field, A and B. I only need to calculate total amount of sales for product A since the beginning of the year. But my function as you can imagine gives me the total sales for product A and B.

(I.E.

Date                  Products   Sales

01.01.2018              A           20

01.01.2018              B           30

01.02.2018              A           10

01.02.2018              B           20

)

The function holds 50 for January and 80 for February. But I need to hold 20 for January and 30 for February.

I tried to use an if condition everywhere in the formula but couldn't succeed.

Can anyone help me where to put an if condition or how to make a set analysis for this condition ?

Thanks a lot.

[sunny_talwar · Accepted solution]

May be this

RangeSum(Below(Sum({<Products = {'A'}>} [Sales]), 0, ($(vMonthCalc)+1)))

[mustafatanyer]

‌worked fine thanks a lot

[jazzsran]

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