Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

tomovangel
Contributor III

Get previous month/year selected dates

Hello dear Qlikers !

So my client wanted to have a CY/PY set analysis so he can look his sales during previous periods .

I have set analysis for:

week: Sum({<Week = {"$(=week(max(Date)-7))"}>}SalePrice)

and for the same month but previous year
=sum({<Year={$(=max(Year),-1)},Month={$(=month(max(Date)))}>}SalePrice)

But now my requirements are different.

My client wants to choose between set of dates,  and see the previous set of dates month/year
for example

02/01/2017 until 03/15/2017 (MM/DD/YYYY)  ( basically the whole february + 15 days from march)

And he wants to see for the previous period of this selected dates, which in this example will sales for 01/01/2017 until 02/15/2017( the whole january + 15 days from february)

Another example is .
from  07/07/2017 until 07/18/2017 - Selected Dates
from  06/07/2017 until 06/18/2017 - Previous dates ( automatically calculated)


I have tried using variables for StartDate and EndDate and input extension, but it crashes so my client doesn't want to use extensions anymore.


What is the workaround that you have found?

Thanks in advance for the response

Best Regards, Angel Tomov Junior Qlik Developer

1 Solution

Accepted Solutions
thiago_justen
Valued Contributor III

Re: Get previous month/year selected dates

What  about not formatting date:

Sum({<Date = {">=$(=AddMonths(Min(Date), -1))<=$(=AddMonths(Max(Date), -1))"}>}SalePrice)

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
23 Replies

Re: Get previous month/year selected dates

My client wants to choose between set of dates,  and see the previous set of dates month/year
for example

02/01/2017 until 03/15/2017 (MM/DD/YYYY)  ( basically the whole february + 15 days from march)

So, you client will select all dates between 02/01/2017 untill 03/15/2017? and basically show the last year equivalent of this?

tomovangel
Contributor III

Re: Get previous month/year selected dates

Yes, this is the first example,

the second is if he chooses between 02/01/2017 and 02/07/2017, the formula to return the same dates but for the previous month. ex: 01/01/2017 and 01/07/2017 .

Re: Get previous month/year selected dates

Try this

Selected Period

Sum(SalePrice)

One Month Back

Sum({<Date = {"$(='>=' & Date(AddMonths(Min(Date), -1), 'MM/DD/YYYY') & '<=' & Date(AddMonths(Max(Date), -1), 'MM/DD/YYYY'))"}>}SalePrice)

thiago_justen
Valued Contributor III

Re: Get previous month/year selected dates

Sunny,

Is there any difference between these expressions:

Yours:

Sum({<Date = {"$(='>=' & Date(AddMonths(Min(Date), -1), 'MM/DD/YYYY') & '<=' & Date(AddMonths(Max(Date), -1), 'MM/DD/YYYY'))"}>}SalePrice)



One I'd suggest:

Sum({<Date = {">=$(=Date(AddMonths(Min(Date), -1), 'MM/DD/YYYY')<=$(=Date(AddMonths(Max(Date), -1), 'MM/DD/YYYY'))"}>}SalePrice)



I mean, if I put >= before dollar sign, will it work too?

It's just a doubt I have.

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
tomovangel
Contributor III

Re: Get previous month/year selected dates

Screenshot_223.png

the second column is your expression. It takes the previous month dates correctly, but doesn't sum SalePrice...

tomovangel
Contributor III

Re: Get previous month/year selected dates

I guess if you put >= before the $ sign, the expression crashes after the >=

So it will not work

Re: Get previous month/year selected dates

Should be the same

thiago_justen
Valued Contributor III

Re: Get previous month/year selected dates

I'm not sure if it wil not work...Could you try it on your app? Let's discover together.

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
tomovangel
Contributor III

Re: Get previous month/year selected dates

Yes, i tried it

Community Browser