Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

zahidrahim_ocp
Contributor

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

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)


)



zahidrahim_ocp
Contributor

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

YoussefBelloum
Esteemed Contributor

Re: Simple Last 3 Months sale set Expression

So try to exclude the filter selection on the set analysis

zahidrahim_ocp
Contributor

Re: Simple Last 3 Months sale set Expression

How?

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

neha_sri
Contributor II

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

pradosh_thakur
Valued Contributor III

Re: Simple Last 3 Months sale set Expression

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.

Highlighted
zahidrahim_ocp
Contributor

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

)

zahidrahim_ocp
Contributor

Re: Simple Last 3 Months sale set Expression

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

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)


)

Community Browser