Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
=(ADDMONTHS(Max(TRX_DATE),-3))
Regards,
Zahid Rahim
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)
)
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
So try to exclude the filter selection on the set analysis
How?
I want to get the last 3 months sale from the date selected.
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
Hi
You posted it under scripting so sometimes it confusing .
coming back to the question
please answer the following
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.
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))
)
Dear Pradosh,
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
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)
)