SUM Firstsortedvalue in script

[jsobrinho]

Hi guys,

Is it possible to create a sum Firstsortadedvalue in the script?

Temp:

LOAD * inline [

Customer|Product|OrderNumber|UnitSales|CustomerID

Astrida|AA|1|10|1

Astrida|AA|7|18|1

Astrida|BB|4|9|1

Astrida|CC|6|2|1

Betacab|AA|5|4|2

Betacab|BB|2|5|2

Betacab|DD|12|25|2

Canutility|AA|3|8|3

Canutility|CC|13|19|3

Divadip|AA|9|16|4

Divadip|AA|10|16|4

Divadip|DD|11|10|4

] (delimiter is '|');

FirstSortedValue2:

LOAD Customer,

sum(FirstSortedValue(distinct Product, -UnitSales,2)) as Value

Resident Temp

Group By Customer,Product

;

[sunny_talwar]

May be this

Temp:

LOAD * inline [

Customer|Product|OrderNumber|UnitSales|CustomerID

Astrida|AA|1|10|1

Astrida|AA|7|18|1

Astrida|BB|4|9|1

Astrida|CC|6|2|1

Betacab|AA|5|4|2

Betacab|BB|2|5|2

Betacab|DD|12|25|2

Canutility|AA|3|8|3

Canutility|CC|13|19|3

Divadip|AA|9|16|4

Divadip|AA|10|16|4

Divadip|DD|11|10|4

] (delimiter is '|');

FirstSortedValue2:

LOAD Customer,

     Sum(Value) as SumValue

Group By Customer;

LOAD Customer,

     Product,

     FirstSortedValue(distinct Product, -UnitSales,2) as Value

Resident Temp

Group By Customer, Product;

or

Temp:

LOAD * inline [

Customer|Product|OrderNumber|UnitSales|CustomerID

Astrida|AA|1|10|1

Astrida|AA|7|18|1

Astrida|BB|4|9|1

Astrida|CC|6|2|1

Betacab|AA|5|4|2

Betacab|BB|2|5|2

Betacab|DD|12|25|2

Canutility|AA|3|8|3

Canutility|CC|13|19|3

Divadip|AA|9|16|4

Divadip|AA|10|16|4

Divadip|DD|11|10|4

] (delimiter is '|');

FirstSortedValue2:

LOAD Product,

     Sum(Value) as SumValue

Group By Product;

LOAD Customer,

     Product,

     FirstSortedValue(distinct Product, -UnitSales,2) as Value

Resident Temp

Group By Customer, Product;

or

Temp:

LOAD * inline [

Customer|Product|OrderNumber|UnitSales|CustomerID

Astrida|AA|1|10|1

Astrida|AA|7|18|1

Astrida|BB|4|9|1

Astrida|CC|6|2|1

Betacab|AA|5|4|2

Betacab|BB|2|5|2

Betacab|DD|12|25|2

Canutility|AA|3|8|3

Canutility|CC|13|19|3

Divadip|AA|9|16|4

Divadip|AA|10|16|4

Divadip|DD|11|10|4

] (delimiter is '|');

FirstSortedValue2:

LOAD Sum(Value) as SumValue;

LOAD Customer,

     Product,

     FirstSortedValue(distinct Product, -UnitSales,2) as Value

Resident Temp

Group By Customer, Product;

[Anonymous]

Hello  Here is some Script that i made for my works it  could help you  tu understand how to deal with your data

Assume that i have a variable vRecord_CA_Month (Best_month_Sales)

vRecord_CA_Month =Max(aggr(Max(CA_Month),Month_CA,Year_CA))

CA_Month  | Month_CA | Year_CA

1235.1             07             2018

....

Here i fetch for the row whith contains le Max Value.

I have to substract some "CHARGE" from vRecord_CA_Month
CHARGE are stored in another table but corresponding tu Month_CA and Year_CA ... this is my link !!

To make this link i had to create a date that corressponds to  on other table

=Makedate(

(Num#(FirstSortedValue( aggr(Year_CA,Month_CA,Year_CA), aggr(-Max(CA_Month),Year_CA,Month_CA) ),'0')),

(Num#(FirstSortedValue( aggr(Month_CA,Year_CA,Month_CA), aggr(-Max(CA_Month),Month_CA,Month_CA) ),'0'))

)

Expression Result look like ( Dont panic !!!)

Expression  =   vRecord_CA_Month 

-

Sum(

{

<

    [Date.autoCalendar.Month] ={'$(=Month(Makedate((Num#(FirstSortedValue( aggr(Year_CA,Month_CA,Year_CA), aggr(-Max(CA_Month),Year_CA,Month_CA) ),'0')), (Num#(FirstSortedValue( aggr(Month_CA,Year_CA,Month_CA), aggr(-Max(CA_Month),Month_CA,Month_CA) ),'0')) )))'},

        [Date.autoCalendar.Year] = {'$(=Year(Makedate((Num#(FirstSortedValue( aggr(Year_CA,Month_CA,Year_CA), aggr(-Max(CA_Month),Year_CA,Month_CA) ),'0')), (Num#(FirstSortedValue( aggr(Month_CA,Year_CA,Month_CA), aggr(-Max(CA_Month),Month_CA,Month_CA) ),'0')) )))'},

[ODS_GLOBAL.Type de pièce] ={'Avoir'}

>

} [Total HT])