Non moving expression

[amilafdo]

Hi,

Please find the below front end expression & let me know, how to optimize this or provide me better way. This is taking longer time to load application & some time error occurred (expression tome out).

Purpose of this --- when I selecting the one date , I want to take the items of Year to last Month end date (YTLM) average sales <=100 (Exclude month no 9 & 12 sales).

=if(((sum({$<YEAR = {$(=max(YEAR))},MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}-{'9','12'}

//,WEEK = {"<=$(=max({<YEAR={$(=max(YEAR))}>} WEEK))"}

,QUARTERNO = {"<=$(=max({<YEAR={$(=max(YEAR))}>} QUARTERNO))"}

,TRANSDATE = {"<=$(=max({<YEAR={$(=max(YEAR))}>} TRANSDATE)-

(count({$<YEAR = {$(=max(YEAR))}

,MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))>$(=max({<YEAR={$(=max(YEAR))}>} MONTH)-1)"}-{'9','12'}

,TRANSDATE = {"<=$(=max({<YEAR={$(=max(YEAR))}>} TRANSDATE))"}

>} distinct DAY)))"}

>}SECONDARY_INVOICE_QTY))

/

(count({$<YEAR = {$(=max(YEAR))} ,MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH)-1)"}-{'9','12'}

>}MONTH)))>=0 and

((sum({$<YEAR = {$(=max(YEAR))},MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}-{'9','12'}

//,WEEK = {"<=$(=max({<YEAR={$(=max(YEAR))}>} WEEK))"}

,QUARTERNO = {"<=$(=max({<YEAR={$(=max(YEAR))}>} QUARTERNO))"}

,TRANSDATE = {"<=$(=max({<YEAR={$(=max(YEAR))}>} TRANSDATE)-

(count({$<YEAR = {$(=max(YEAR))}

,MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))>$(=max({<YEAR={$(=max(YEAR))}>} MONTH)-1)"}-{'9','12'}

,TRANSDATE = {"<=$(=max({<YEAR={$(=max(YEAR))}>} TRANSDATE))"}

>} distinct DAY)))"}

>}SECONDARY_INVOICE_QTY))

/

(count({$<YEAR = {$(=max(YEAR))} ,MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH)-1)"}-{'9','12'}

>}MONTH)))<=100,

((sum({$<YEAR = {$(=max(YEAR))},MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}-{'9','12'}

//,WEEK = {"<=$(=max({<YEAR={$(=max(YEAR))}>} WEEK))"}

,QUARTERNO = {"<=$(=max({<YEAR={$(=max(YEAR))}>} QUARTERNO))"}

,TRANSDATE = {"<=$(=max({<YEAR={$(=max(YEAR))}>} TRANSDATE)-

(count({$<YEAR = {$(=max(YEAR))}

,MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))>$(=max({<YEAR={$(=max(YEAR))}>} MONTH)-1)"}-{'9','12'}

,TRANSDATE = {"<=$(=max({<YEAR={$(=max(YEAR))}>} TRANSDATE))"}

>} distinct DAY)))"}

>}SECONDARY_INVOICE_QTY))

/

(count({$<YEAR = {$(=max(YEAR))} ,MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH)-1)"}-{'9','12'}

>}MONTH))),'0')

Thanks & Regards

Amila Fernando

[felipedl]

HI Amila,

It's quite difficult to help when the only thing you post is the expression, and being that large it's even harder.

Can you share the app with some test data?

Felipe.

[amilafdo]

Hi Felip,

Sorry for the late reply & Thank you very much for your effort, I understood that the difficulty of this. So can you please let me know whether I can extract Last 12 months data when selecting the particular date?

Thanks

Amila

[romansavchuk]

Hi,

As correctly write @felipedl it`s difficult to work with this formula without application model, but i have some advices, that may help you.

# 1. How YEAR, MONTH and QUARTERNO related to TRANSDATE in data? Did you use master calendar for dates? If yes, may be you need to filter only TRANSDATE field and comment filters of YEAR, MONTH and QUARTERNO as in the following example:

sum(

{$<

//YEAR = {$(=max(YEAR))},

//MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}-{'9','12'}

//,WEEK = {"<=$(=max({<YEAR={$(=max(YEAR))}>} WEEK))"}

//,QUARTERNO = {"<=$(=max({<YEAR={$(=max(YEAR))}>} QUARTERNO))"}

,TRANSDATE = {"<=$(=max({<YEAR={$(=max(YEAR))}>} TRANSDATE)-

(count({$<YEAR = {$(=max(YEAR))}

,MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))>$(=max({<YEAR={$(=max(YEAR))}>} MONTH)-1)"}-{'9','12'}

,TRANSDATE = {"<=$(=max({<YEAR={$(=max(YEAR))}>} TRANSDATE))"}

>} distinct DAY)))"}

>}SECONDARY_INVOICE_QTY).

# 2. You can create separate month field 'SalesMonth' for example, that calculated by following formula:

IF( MonthNumber = 9 OR MonthNumber = 12, NULL(), MonthNumber )

And using this field may automatically exclude data with months 9 and 12, because it has NULL and not included in set analysis.
Of course this advice is not applicable if you configure these months from interface.

[amilafdo]

Hi Roman,

# 2. You can create separate month field 'SalesMonth' for example, that calculated by following formula:

IF( MonthNumber = 9 OR MonthNumber = 12, NULL(), MonthNumber )

***Thanks for your reply & the above was helpful to avoid months.

[amilafdo]

Hi,

If anyone know how to take the last 12 months (up to last month end date) average sales when selecting the one date, please publish the expression. Below is my calendar with period.

Calender:

Load

     DATE(TRANSDATE) as TRANSDATE,

     YEAR(ADDMONTHS(TRANSDATE,-3)) AS YEAR,

TEXT(MONTH(TRANSDATE)) AS MONTHLINK,

Week(ADDMONTHS(TRANSDATE,-3)) as WEEK,

Day(TRANSDATE) as DAY,

WeekDay(BILL_TO_CUSTOMER) as WEEKDAY

Resident Stock;

Concatenate

LOAD

Date(Date) as TRANSDATE,

YEAR(ADDMONTHS(Date,-3)) AS YEAR,

TEXT(MONTH(Date)) AS MONTHLINK,

Week(ADDMONTHS(Date,-3)) as WEEK,

Day(Date) as DAY,

WeekDay(Date) as WEEKDAY

;

LOAD date($(vStartDate) + IterNo()) as Date

AUTOGENERATE 1

WHILE $(vStartDate) + IterNo() <= $(vEndDate)

;

Period:

LOAD * INLINE [

    MONTHLINK, MONTH, QUARTER,Monthday,QUARTERNO

    Apr, 1, Q1,1,1

    May, 2, Q1,2,1

    Jun, 3, Q1,3,1

    Jul, 4, Q2,4,2

    Aug, 5, Q2,5,2

    Sep, 6, Q2,6,2

    Oct, 7, Q3,7,3

    Nov, 8, Q3,8,3

    Dec, 9, Q3,9,3

    Jan, 10, Q4,10,4

    Feb, 11, Q4,11,4

    Mar, 12, Q4,12,4

];

Thanks

Amila Fernando