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

[#1]

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

[SunilChauhan]

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)

[#3]

Hi,

Thanks for your reply !

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

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>

[SunilChauhan]

-          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

[#5]

Thanks for trying to walk me through this.

I am sorry but its not working for me. I guess its really complicated to

adjust all the fields correctly.

For example, you suggest to create date(datetime) but then what field the

set-analysis will modify ? I don’t have date(datetime) as a field that can

be selected.

In your specific example - =sum({$<Date={">$(=Date(Max(

Date)-7))<=$(=Max(Date))"}>}Amount) are all 'Date' the same ? is it month

? timestamp or always "Date(timestamp)". In this case, does it mean I need

to change my calender.

Not sure if relevant, but this is the link to a "reduced" QVW.

https://www.dropbox.com/s/jn2ol14413t9yim/Nprinting-reduced.qvw

Thanks

Gur

jive-511714171-2b7d-2-6c98@community.qlik.com

Thanks.

>

From: luminary Sahatwar

Sent: Tuesday, January 1, 2013 5:37 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/295820#295820>

[#6]

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!