topic What's wrong with this Set analysis with Date and Date# in QlikView

What's wrong with this Set analysis with Date and Date#

Wed, 25 Feb 2015 11:58:37 GMT

Hello everyone,

Here's the expression I try to use to calculate sales of last month>

=sum({<Date={"$(=date(addmonths(date#(Month&'/'&Year ,'MMM/YYYY'),-1),'MM/YYYY'))"}>}[sales])

The expression: date(addmonths(date#(Month&'/'&Year ,'MMM/YYYY'),-1),'MM/YYYY') gives the right result '01/2015'

and: sum({<Date={"01/2015"}>}[sales]) also gives the right result.

however it doesn't work if I combine them.

Can you advise please? THanks a lot!

Re: What's wrong with this Set analysis with Date and Date#

MK_QSL — Wed, 25 Feb 2015 12:02:01 GMT

What is the format of your Date field?

Re: What's wrong with this Set analysis with Date and Date#

Wed, 25 Feb 2015 12:03:51 GMT

the format of Date field is "DD/MM/YYYY"

Month format= 'Jan','Feb' etc

Re: What's wrong with this Set analysis with Date and Date#

MK_QSL — Wed, 25 Feb 2015 12:06:04 GMT

Then how can you compare

DD/MM/YYYY with MM/YYYY ?

Below is wrong...

sum({<DD/MM/YYYY={"MM/YYYY"}>}[sales])

Re: What's wrong with this Set analysis with Date and Date#

Wed, 25 Feb 2015 12:13:22 GMT

Both of them are Date, no? Could you please explain? THanks

How should I modify the set analysis so I get the sales of January?

Do i have to write sum({<Year={$(=Year(addmonths(Date#(Month&'/'&Year ,'MMM/YYYY'),-1)))},Month={$(=Month(addmonths(Date#(Month&'/'&Year ,'MMM/YYYY'),-1)))}>}sales)?

Thank!

Re: What's wrong with this Set analysis with Date and Date#

MK_QSL — Wed, 25 Feb 2015 12:18:20 GMT

=SUM({<Date = {'>=$(=Date(MonthStart(AddMonths(Today(),-1)),'DD/MM/YYYY'))<=$(=Date(MonthEnd(AddMonths(Today(),-1)),'DD/MM/YYYY'))'}>}sales)

=SUM({<Date = {'>=$(=Date(MonthStart(AddMonths(Max(Date),-1)),'DD/MM/YYYY'))<=$(=Date(MonthEnd(AddMonths(Max(Date),-1)),'DD/MM/YYYY'))'}>}sales)