21 Replies Latest reply: Jan 3, 2018 7:14 AM by Sunny Talwar

# Simple Last 3 Months sale set Expression

Dear Experts,

I am trying to get last 3 months sale but getting only the date selected one day sale. i am using below expression:

SUM({<TRX_DATE = {"\$(='>=' & v3MonthsBackDate)"}>}

(IF (TYPE = 'CM', QUANTITY_CREDITED , QUANTITY_INVOICED)/ IF(isnull(LTR_CV_RATE),1,LTR_CV_RATE))

)

where v3MonthsBackDate is a variable with below syntax:

Regards,

Zahid Rahim

• ###### Re: Simple Last 3 Months sale set Expression

Hi,

try this:

.

=if(TYPE = 'CM' and isnull(LTR_CV_RATE),

SUM({<TRX_DATE = {">=\$(=v3MonthsBackDate)"}>} QUANTITY_CREDITED ),

SUM({<TRX_DATE = {">=\$(=v3MonthsBackDate)"}>} QUANTITY_INVOICED) /

SUM({<TRX_DATE = {">=\$(=v3MonthsBackDate)"}>} LTR_CV_RATE)

)

• ###### Re: Simple Last 3 Months sale set Expression

Dear Yousaf,

Thank you for the solution. But the problem is with filter, If i remove the filter it shows me accurate 3 months back sale from the current date and if i give any date in the filter then it shows me the filter date selected sale only.

Regards,

Zahid Rahim

• ###### Re: Simple Last 3 Months sale set Expression

So try to exclude the filter selection on the set analysis

• ###### Re: Simple Last 3 Months sale set Expression

How?

I want to get the last 3 months sale from the date selected.

• ###### Re: Simple Last 3 Months sale set Expression

Hi

You posted it under scripting so sometimes it confusing .

coming back to the question

1: Are you using a straight table/pivot?

2: Do you have Date or date related field in the table?

if not than post a screenshot of your table.

• ###### Re: Simple Last 3 Months sale set Expression

It is a simple table.

Date field is in the table with the name TRX_DATE.

Can you please share/convert my expression to as-of table....

Regards,

Zahid Rahim

• ###### Re: Simple Last 3 Months sale set Expression

can i get a screen shot of you table. if you want rolling sum for last 3 month than only you will need as-of -table or else not.

try this first

=if(TYPE = 'CM',

SUM({<TRX_DATE = {">=\$(=v3MonthsBackDate)"}>} QUANTITY_CREDITED ),

SUM({<TRX_DATE = {">=\$(=v3MonthsBackDate)"}>} QUANTITY_INVOICED) /

alt(SUM({<TRX_DATE = {">=\$(=v3MonthsBackDate)"}>} LTR_CV_RATE),1)

)

• ###### Re: Simple Last 3 Months sale set Expression

try removing the dollar before the variablein the set expression

• ###### Re: Simple Last 3 Months sale set Expression

dis you try my suggestion ? This didn't work?

• ###### Re: Simple Last 3 Months sale set Expression

Hi Rahim,

It feels like you need to by pass the date field which is used in your filter.

SUM({<Date=,TRX_DATE = {"\$(='>=' & v3MonthsBackDate)"}>}

(IF (TYPE = 'CM', QUANTITY_CREDITED , QUANTITY_INVOICED)

/

IF(isnull(LTR_CV_RATE),1,LTR_CV_RATE))

)

Where Date is the field which you are using in the filter

Regards,

Neha

• ###### Re: Simple Last 3 Months sale set Expression

I have changed the expression to below, but no success. Further my Date/ FIlter date field is TRX_DATE as well

SUM({<TRX_DATE=,TRX_DATE = {">=\$(v3MonthsBackDate)"}>}

(IF (TYPE = 'CM', QUANTITY_CREDITED , QUANTITY_INVOICED)/ IF(isnull(LTR_CV_RATE),1,LTR_CV_RATE))

)

• ###### Re: Simple Last 3 Months sale set Expression

Hi Rahim,

Could you please send some sample data.

What I understand is on every date you need sum of last 3 months sales

(IF (TYPE = 'CM', QUANTITY_CREDITED , QUANTITY_INVOICED)/ IF(isnull(LTR_CV_RATE),1,LTR_CV_RATE))

)

Regards,

Neha

• ###### Re: Simple Last 3 Months sale set Expression

Two questions

1) Are you selecting in TRX_DATE field or another field?

2) What is the format for TRX_DATE? Do you see the same format when you put this ='>=' & v3MonthsBackDate a text box object?

• ###### Re: Simple Last 3 Months sale set Expression

1. Same Field i-e TRX_DATE

2. Format is same i have checked in a textbox MM/DD/YYYY the default.

• ###### Re: Simple Last 3 Months sale set Expression

Don't see anything wrong with your expression.... But tried to simplify the denominator using Alt function... see if that changes anything?

Sum({<TRX_DATE = {"\$(='>=' & v3MonthsBackDate)"}>} If(TYPE = 'CM', QUANTITY_CREDITED, QUANTITY_INVOICED)/Alt(LTR_CV_RATE,1))

• ###### Re: Simple Last 3 Months sale set Expression

Dear Sunny,

When i remove the filter then i can see last 3 months sale perfectly. But when i apply the filter and select any date then i can see only that date sale.

Regards,

Zahid Rahim

• ###### Re: Simple Last 3 Months sale set Expression

Hahahahaha I have read this above... I understand your issue... but just from your expression I don't see why this would happen... your expression looks absolutely correct to me. do you have a calculated dimension?

• ###### Re: Simple Last 3 Months sale set Expression

No there is no calculated dimension.

• ###### Re: Simple Last 3 Months sale set Expression

no idea zahid because like I mentioned your expression looks good to me.... but I might be missing something... may be someone else might be able to find the mistake

• ###### Re: Simple Last 3 Months sale set Expression

Dear Sunny,

I have manage to fix it by introducing a Master Calendar which is an independent table not joined with existing data model and then use the calendar date in filter.

Please inform is this good approach?

Regards,

Zahid Rahim

• ###### Re: Simple Last 3 Months sale set Expression

If it isn't slowing down you app, I would say you can use it... but ideally, it is not a good idea to have an island table drive your charts as it forms a Cartesian Product with your data slowing down your app... If you don't see the slowing down, then you should be fine