topic Re: Latest Month EndDate Sales only in QlikView

Latest Month EndDate Sales only

Tue, 08 Mar 2016 07:42:29 GMT

hi guys i want to get the latest month end date sales only that means, if present month is march 8 in that case i need to get feb 29 sales.

Can any one help how to achieve that in expression sum(Sales)

Thanks in advance!

Vijay

Re: Latest Month EndDate Sales only

kaushiknsolanki — Tue, 08 Mar 2016 08:15:32 GMT

Hi,

Use this expression.

Sum({<Date = {"$(=monthend(addmonths(Max(Date),-1)))"}>}Sales)

Regards,

Kaushik Solanki

Re: Latest Month EndDate Sales only

nagireddy_qv — Tue, 08 Mar 2016 08:23:01 GMT

please try this exp..

Sum({<Date = {"$(=addmonths(monthend(Max(Date))))"}>}LineSalesAmount)

Re: Latest Month EndDate Sales only

Tue, 08 Mar 2016 08:24:21 GMT

Hi kaushik,

your expression will give march 31, In my case , if march 31 still not there then it should take feb 29 date.

Thanks

Re: Latest Month EndDate Sales only

its_anandrjs — Tue, 08 Mar 2016 08:27:47 GMT

Try this for the last months complete sale on last date.

=Monthend( AddMonths( Max(Today() ), -1) )

Regards,

Anand

Re: Latest Month EndDate Sales only

Tue, 08 Mar 2016 08:35:11 GMT

Hi nagi reddy the Addmonths(,) should be like this wrong syntax for add months.

Thanks for reply

Re: Latest Month EndDate Sales only

kaushiknsolanki — Tue, 08 Mar 2016 09:11:27 GMT

Hi,

My expression will give Feb 29 when your max(date) is 8th March. This is what you have asked in question, correct me If I am Wrong.

Sum({<Date = {"$(=monthend(addmonths(Max(Date),-1)))"}>}Sales)

Regards,

Kaushik Solanki

Re: Latest Month EndDate Sales only

tresB — Tue, 08 Mar 2016 09:20:14 GMT

Define a variable like:

vRecentMostMonthEnd =If(Today()=Floor(Monthend(Today())), Today(), MonthEnd(Today(),-1))

Then expression like:

Sum({<Date={'$(vRecentMostMonthEnd)'}>} Sales)

Edit: corrected a brace

Re: Latest Month EndDate Sales only

Tue, 08 Mar 2016 09:37:46 GMT

yes this is correct to get last month last date. in future i get march 31st also at that also it will give only last month last date.

i need a condition if last date is not there in current month than take last month last date....

thanks anand

Re: Latest Month EndDate Sales only

Tue, 08 Mar 2016 09:42:37 GMT

ya that only i needed buy ur set condition is not working.

Thanks

Re: Latest Month EndDate Sales only

Tue, 08 Mar 2016 09:46:46 GMT

Thanks a lot i will try and tell i hope it works

Re: Latest Month EndDate Sales only

ahmar811 — Tue, 08 Mar 2016 09:52:46 GMT

vijay krishna

if you have Month field please try this expression

Sum({<Month= {"$(=Month(Max(Date))-1 )"}>}Sales)

hope this help you

Thanks & Regards

Ahmar Ansari

Re: Latest Month EndDate Sales only

Tue, 08 Mar 2016 10:17:12 GMT

this is one is fine but instead of today i want last date in my records. for that max (Date) we can use ....but here in this variable how can use max (Date)

Re: Latest Month EndDate Sales only

amayuresh — Tue, 08 Mar 2016 10:33:18 GMT

Hi Vijay,

Try this expression for last date if your Filter is Order Date

=sum({<OrderDate={'$(=monthend(Addmonths(OrderDate,-1)))'}>}TotalDue)

Re: Latest Month EndDate Sales only

tresB — Tue, 08 Mar 2016 10:39:46 GMT

You mean, like:

vRecentMostMonthEnd =If(max(Floor(Date))=Floor(Monthend(Today())), Date(Max(Date)), MonthEnd(Today(),-1))

Re: Latest Month EndDate Sales only

Tue, 08 Mar 2016 11:22:57 GMT

ya but can we write the condition without today() function.

Re: Latest Month EndDate Sales only

tresB — Tue, 08 Mar 2016 11:31:55 GMT

Certainly, I just missed that. May be:

vRecentMostMonthEnd =If(max(Floor(Date))=Floor(Monthend(Today())),Date(Max(Date)), MonthEnd(Max(Date),-1))

Re: Latest Month EndDate Sales only

amayuresh — Tue, 08 Mar 2016 11:51:10 GMT

=sum({<OrderDate={'$(=monthend(Addmonths(OrderDate,-1)))'}>}TotalDue)

This works for you using Order Date List box

Re: Latest Month EndDate Sales only

Tue, 08 Mar 2016 12:16:16 GMT

ya correct one when i take that if expression in text area it's working fine giving correct date...but when i use in set expression showing 0.000 value

vRecentMonth=date(If(max(Floor(StockClosing_DateNum))=Floor(Monthend(num(Today()))),num(Max(StockClosing_DateNum)), MonthEnd(Max(StockClosing_DateNum),-1)))

in expression =sum({<BILLDATE={'$(vRecentMostMonth)'}>}StockClosing_GROSSWEIGHT)

Stockclosing_date is floor(BILLDATE)

BILLDATE is normal date field

Re: Latest Month EndDate Sales only

Tue, 08 Mar 2016 12:45:51 GMT

Thanks Mayuresh the condition is correct but for my expression it's giving zero value....

i am taken your condition in text area it's working fine with small change '=max(MonthEnd(Addmonths(Date,-1)))'

when i use for my measure it's giving zero value...