Revenue Adjustment Month-on-Month

[poojashribanger]

Hi All,

I have a table with Multiple entries for date.

Example:

Output for Jan will be 700-100=600

Feb will be 150-200=-50

Now i want to deduct Value from table 2 from the overall value of Jan from table 1  in data load editor but without aggregating.

Is it possible as i already have a data model in place with almost 15 different tables and i can't aggregate the data at the backend.

Thanks in |Advance

Regards,

Poojashri

[Qrishna]

So you wanna do it in load editor or on UI? if you wanna do it backend without disturbing the existing Data model, try creating a new resident load table and work on it, On UI its doable but seems a bit complex compared to the backed logic when the Full datamodel is unknown. But from the picture you have provided, we could achive it using Rangesum(Above()). 

for you understanding i have split the expression:

Measure1 = Sum(Sales)
Measure2 = Sum({$<Product={'Eraser'}>}Sales)
Measure3 = RangeSum(Above(Measure2,0,RowNo()))
Measure_Final = Measure1-Measure3

combined expression:
Sum(Sales) - RangeSum(Above(Sum({$<Product={'Eraser'}>}Sales),0,RowNo()))

[marcus_sommer]

It's quite simple within the data-model by concatenating both tables, for example:

t: load Date, Product, Sales as Value, 'Sales' as Source
from A;
concatenate(t)
   load Adjustment as Date, 'Adjustment' as Product, -Value as Value, 'Adjustment' as Source
from B;

[Kushal_Chawda]

@poojashribanger  if both your tables are linked on date you can simply write below expression

sum(sales) - sum(adjustedvalue)