Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zahidrahim_ocp
Partner - Creator
Partner - Creator

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:


=(ADDMONTHS(Max(TRX_DATE),-3))


Regards,


Zahid Rahim

21 Replies
YoussefBelloum
Champion
Champion

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)


)



zahidrahim_ocp
Partner - Creator
Partner - Creator
Author

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

YoussefBelloum
Champion
Champion

So try to exclude the filter selection on the set analysis

zahidrahim_ocp
Partner - Creator
Partner - Creator
Author

How?

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

neha_sri
Creator III
Creator III

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

pradosh_thakur
Master II
Master II

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.

Learning never stops.
zahidrahim_ocp
Partner - Creator
Partner - Creator
Author

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))

)

zahidrahim_ocp
Partner - Creator
Partner - Creator
Author

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

pradosh_thakur
Master II
Master II

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)


)

Learning never stops.