5 Replies Latest reply: Dec 10, 2013 3:18 PM by Kevin Bertsch

# Set-analysis - selecting the last 7 days and all days in  a month

Hi,

I am trying to have a textbox which will show me a summary, regarding of the selection, of a value (revenue).

So I need:

1) Revenue Last 7 days (including weekends and crossing back to previous month if needed.

2) Revenue month-to-date - all days in the current month-year pair - till today.

For the LAST-7-Days:

I have "timestamp" and also DateMonth, DateYear, DateDay.

I tried the "bookmark" approach, creating a listbox, and setting the query, and then saving the BookMark.

=if(DateTime>=now()-8,if(DateTime<now()-1,DateTime

The I use it in the textbox: =sum({BM_Last_7_Days}revenue)

But the problem is that when there are other selections active, it's not working

For the month-to-date:

I just used

=DateMonth=\$(=Max(DateMonth))

But that's really a bad implementation. It doesn't work across years (like today

Would appriciate any help,

Thanks

• ###### Re: Set-analysis - selecting the last 7 days and all days in  a month

use below code for Mtd

=sum({\$<Year={\$(=Max(Year))},Month={">\$(=Max({<Year={\$(=Max(Year)))}>}Month)-1)<=\$(=Max({<Year={\$(=Max(Year)))}>}Month))"}>}Amount)

and below for last 7 Days

=sum({\$<Date={">\$(=Date(Max(Date)-7))<=\$(=Max(Date))"}>}Amount)

• ###### Re: Set-analysis - selecting the last 7 days and all days in  a month

Hi,

Seems like you need to be a professor for that kind of functionality K

I tried them but have two questions:

-          For the "month to date". Seems that is assumes the year is

selected ? or is it using now() in any way ?

-          I noticed that the answer changed id I manually select a year.

Can this be defined to ignore any selection ?

For the last 7 days – I think I didn't translate it correctly. I used the

following: sum({\$<DateTime={

">\$(=DateTime(Max(Datetime)-7))<=\$(=Max(DateTime))"}>}

But DateTime is 'timestamp'. Should I be using "DateDay" in any of the

expression variables ? where DateDay is basically Day(DateTime) ?

Sorry for asking what is probably very basic.

Thanks

From: luminary Sahatwar qcwebmaster@qlik.com

Sent: Tuesday, January 1, 2013 5:07 PM

To: gurlavie100

Subject: - Re: Set-analysis - selecting the last 7 days

and all days in a month

QlikCommunity <http://community.qlik.com/index.jspa>Re: Set-analysis -

selecting the last 7 days and all days in a month

created by luminary

Sahatwar<http://community.qlik.com/people/itsunil.chauhan>in

New to QlikView - View the full

discussion<http://community.qlik.com/message/295817#295817>

• ###### Re: Set-analysis - selecting the last 7 days and all days in  a month

-          For the "month to date". Seems that is assumes the year is

selected ? or is it using now() in any way ?

By default it will take Max Month otherwise selected one

-          I noticed that the answer changed id I manually select a year.

Can this be defined to ignore any selection ?

We Can ignore selection using = in set analysis

ex: sum({<Year=,Month=,>}amount) will ignore  Year and so Month

sum({\$<DateTime={

">\$(=DateTime(Max(Datetime)-7))<=\$(=Max(DateTime))"}>}

Create a date field from Date Time using Date(dateTime)

hope this helps

• ###### Re: Set-analysis - selecting the last 7 days and all days in  a month

Hi,

One thing I found out that drove me crazy until I, you know, READ the documentation..

Now() takes a parameter:

Now(0) is the time the report was last reloaded.

Now(1) is the time when you called the function

Now(2) is the time when the document was opened

The documentation says the default value is 1, but I had a hard time with reports that I hadn't reloaded, trying to figure out what "Yesterday" was returning 3 days ago. I think the default value is "0".

Now that I'm aware of it, I just make sure I reload reports before I do automated printing, and explicitly use now(0).

Documentation warns against prolific use of "now(1)", as this polls the system timer every second, and could degrade system performance.

Same holds true for the "today()" function.

Just FYI!