Get values for the last n months

[#1]

Hello

I am currently having problems regarding this situation, I need to get the customers with no quantity based on the

month the user selected and the condition specified.

I will attach my qvd file for you to analyze the problem more clearly.

Thank you!

Regards,

Donna

p.s Volume = Qty

[#2]

bump

[#3]

bringing up my post again

[jagan]

Hi,

PFA document.

Regards,

Jagan.

[#5]

Hi,

Thank you for the reply and your solution. Can you give me a brief explanation for the solution you have given?

Thank you!

[jagan]

Hi,

The expression

=Sum({<Year=, Month=, Date={'>=$(=MonthStart(Max(Date), (Max(no_vol)-1)*-1))<=$(Max(Date))'}>}Qty)

Year=  Excludes Year Selection

Month= Excludes Month Selection

Date={'>=$(=MonthStart(Max(Date), (Max(no_vol)-1)*-1))<=$(Max(Date))'} (Filters the date between start and end date)

=MonthStart(Max(Date), (Max(no_vol)-1)*-1) - Gets Max date and max no_vol (number of months) selected and subtract no_vol months from Max date.

MonthStart() will give you the starting date of the month for a given date

(Max(no_vol)-1)*-1 = If you select 12 months then 1 should be subtracted to get 12 months back date

Hope this helps you.

Regards,

Jagan.

[#7]

thanks for the explanation

although I still have a problem, if I select 3months no volume, the table should also display the month selected [select all customers in the selected month with no volume], then the previous month should also no volume, and so on. base on the expression you have created, ex: May[selected], even though April has a volume, it will still display. It should only display the list of customers with no volume depending on the selected no. of months.

'3 months without volume' means that for 3 consecutive months, the customer doesn't have any volume

Thanks!