aggr into script

[surajdhall]

Hi Experts,

I have an aggr expression that i want to move into the script to improve the performance, but i am not sure.

The expression is in the Trend dimension column in the pivot tab.

Could you please help.

[sunny_talwar]

This is going to be a fairly huge initiative to carry this out in the script... are you sure you want to do this?

[surajdhall]

Hi Sunny,

The expression is taking long time to calculate in UI. So i want to move it to backend. If it is very huge task, then can i move part of it to the backend.

Thanks!

[sunny_talwar]

I mean once you decide to move it...I would move the whole thing... but you realize that once you move this to the backend, your values won't really change based on selections? For instance if you select a monthyear, it may not respond to it the way you would expect if you keep this as a calculated dimension?

[surajdhall]

Hi Sunny,

I am fine with that. Please help to move it to backend.

Thanks!

[sunny_talwar]

It will take some time, but will work on it....

[pradosh_thakur]

Hi Suraj

Try this as your  calculated dimension and see if it makes any difference or optimizes a little

=Aggr(If(FirstSortedValue(TOTAL <Product, Status>

                            Aggr(Round((sum(Margin)/sum(Sales)),0.0001)- (Round(Alt(Sum(Margin)/Sum(Sales), 0),0.0001)*0.005),

                            Product, Status, MonthYear), Aggr(MonthYear, Product, Status, MonthYear))

                            >

         FirstSortedValue(TOTAL <Product, Status>

                            Aggr(Round((sum(Margin)/sum(Sales)),0.0001),

                            Product, Status, MonthYear), -Aggr(MonthYear, Product, Status, MonthYear)),

   'Bad Trend',

pick(match(Sum(TOTAL <Product, Status>

    Aggr(if(not IsNull(Trim(Below(sum(Margin)/sum(Sales)))),

   If(round(Alt(Below(Sum(Margin)/Sum(Sales)), 0),0.0001) < (Round(Alt(Sum(Margin)/Sum(Sales), 0),0.0001) - (Round(Alt(Sum(Margin)/Sum(Sales), 0),0.0001)*0.005)),1,

   0),0),   

    Product, Status,MonthYear)

       ),0,1)+1, 'Average Trend','Good Trend','Positive Trend'))

,

   Product, Status)

regards

Pradosh

[surajdhall]

Hi Pradhosh,

Thanks. I see some improvement in cal time, but it is still heavy to calculate.

[sunny_talwar · Accepted solution]

Check the attached script

Data:

LOAD *,

      Date(MonthStart(Date),'MMM-YYYY') as MonthYear

    INLINE [

    Product, Date, Margin, Sales,Status

    A, 01/01/2017, 1184632, 4227592,High

    A, 01/02/2017, 1273124, 4545040,High

    A, 01/03/2017, 676862, 2272720,High

    A, 01/04/2017, 678862, 2272720,High

    A, 01/05/2017, 679962, 2272720,High   

    A, 01/01/2017, 592316, 2113796,Low

    A, 01/02/2017, 592316, 2113796,Low

    A, 01/03/2017, 592316, 2113796,Low

    A, 01/04/2017, 592316, 2113796,Low

    A, 01/05/2017, 592316, 2113796,Low

    B, 01/01/2017, 5, 52676,High

    B, 01/02/2017, 6, 62803,High

    B, 01/03/2017,,,High

    B, 01/04/2017, 12, 96803,High

    B, 01/05/2017, 7, 5267,High

    B, 01/01/2017, 5, 852676,Low

    B, 01/02/2017, 63, 362803,Low

    B, 01/03/2017, 105, 2552676,Low

    B, 01/04/2017, 132, 6803,Low

    B, 01/05/2017, 75, 567,Low

    C, 01/01/2017, 24, 243,High

    C, 01/02/2017, 24, 243,High

    C, 01/03/2017, 371, 464,High

    C, 01/04/2017, 353, 78,High

    C, 01/05/2017, 22, 435,High   

    C, 01/01/2017, 224, 244,Low

    C, 01/02/2017, 224, 246,Low

    C, 01/03/2017, 39, 449,Low

    C, 01/04/2017, 33, 738,Low

    C, 01/05/2017, 212, 455,Low

    D, 01/01/2017, 37769, 379069,High

    D, 01/02/2017, 4219, 42795,High

    D, 01/03/2017, 377669, 375069,High

    D, 01/04/2017, 42459, 424795,High

    D, 01/05/2017, 6569, 392469,High

    D, 01/01/2017, 37739, 379269,Low

    D, 01/02/2017, 42124659, 424795,Low

    D, 01/03/2017, 37765269, 3795069,Low

    D, 01/04/2017, 424659, 42734795,Low

];

Aggr1:

LOAD Round((sum(Margin)/sum(Sales)),0.0001)-(Round(Alt(Sum(Margin)/Sum(Sales), 0),0.0001)*0.005) AS Cal1,

Round((sum(Margin)/sum(Sales)),0.0001) as Cal2,

Previous(Product),

Previous(Status),

Product,

Status,

MonthYear

Resident Data

Group By Product, Status, MonthYear;

Aggr2:

LOAD *,

If(Product = Previous(Product) and Status = Previous(Status), If(Peek('Cal2') < Cal1, 1, 0), 0) as Cal3

Resident Aggr1

Order By Product, Status, MonthYear desc;

Left Join (Data)

LOAD Product,

Status,

FirstSortedValue(Cal1, MonthYear) as Cal1,

FirstSortedValue(Cal2, -MonthYear) as Cal2,

Sum(Cal3) as Cal3,

If(FirstSortedValue(Cal1, MonthYear) > FirstSortedValue(Cal2, -MonthYear), 'Bad Trend',

If(Sum(Cal3) = 0, 'Good Trend',

If(Sum(Cal3) = 1, 'Positive Trend'))) as Trend

Resident Aggr2

Group By Product, Status;

DROP Table Aggr1, Aggr2;

[surajdhall]

I am very thankful to you. I really appreciate your time and effort.