Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
amilafdo
Creator
Creator

Non moving expression

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

5 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

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
Creator
Creator
Author

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
Contributor III
Contributor III

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
Creator
Creator
Author

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
Creator
Creator
Author

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