topic Re: Get values for the last n months in QlikView

Get values for the last n months

Mon, 26 Nov 2012 21:46:56 GMT

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

Re: Get values for the last n months

Tue, 27 Nov 2012 13:52:59 GMT

bump

Re: Get values for the last n months

Wed, 28 Nov 2012 00:26:02 GMT

bringing up my post again

Re: Get values for the last n months

jagan — Wed, 28 Nov 2012 06:17:44 GMT

Hi,

PFA document.

Regards,

Jagan.

Re: Get values for the last n months

Wed, 28 Nov 2012 07:21:59 GMT

Hi,

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

Thank you!

Re: Get values for the last n months

jagan — Wed, 28 Nov 2012 08:34:33 GMT

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.

Re: Get values for the last n months

Thu, 29 Nov 2012 02:10:46 GMT

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!