topic Re: Last 7 days collection?? in QlikView

Last 7 days collection??

narender123 — Mon, 08 Jul 2013 11:39:30 GMT

Hi All,

I want to calculate last seven days collection .I am using the expression like:

=count({<Year={$(=max(Year))},Month={$(=MaxString(Month))},Day={'>=$(=Day(Today()-8))'},Status={'1'}>}PreReleaseNo) //not working

if i uses this

=count({<Year={$(=max(Year))},Month={$(=MaxString(Month))},Day={'>=$(=Day(Today()-7))'},Status={'1'}>}PreReleaseNo) //working

because today is 8july2013 so it will calculate this month days collection but when i used more than 7 days then it will not calculate because that day fall in the previous month.So it is not calculate the when the days fall into previous month day.

Please Solve this??

Thanks in advance.

Narender

Re: Last 7 days collection??

tresB — Mon, 08 Jul 2013 11:44:47 GMT

Try this:

=count({<Year=,Month=,Day={'>=$(=Day(Today()-8))'},Status={'1'}>}PreReleaseNo)

Re: Last 7 days collection??

narender123 — Mon, 08 Jul 2013 11:58:24 GMT

Thanks Mohit,

My date format is in MM/DD/YYYY fromat

like this one:

Date={">=$(=Date(Today()-8,'MM/DD/YYYY))"}

Still its not working for 8th days but running for this month days like Today()-7

Thanks.

Any idea??

Re: Last 7 days collection??

narender123 — Mon, 08 Jul 2013 12:03:16 GMT

Thanks tresesco .Its not working as it will select all year and all days and giving wrong result.

Re: Last 7 days collection??

er_mohit — Mon, 08 Jul 2013 12:08:07 GMT

simply try this with sign' > 'or '>='

count(distinct {$<Datefield={">=$(=Date(Today()-8,'MM/DD/YYYY'))"},Status={'1'}>}PreReleaseNo)

count(distinct {$<Year=,Month=,Datefield={">=$(=Date(Today()-8,'MM/DD/YYYY'))"},Status={'1'}>}PreReleaseNo)

Re: Last 7 days collection??

tresB — Mon, 08 Jul 2013 12:14:14 GMT

You are right. In that case why don't you try with Date instead of Day?

Re: Last 7 days collection??

jagan — Mon, 08 Jul 2013 12:21:47 GMT

Hi Narendar,

If you date dimension then use this expression

=count({<Year=,Month=,Day=,

DateDimensionName={">=$(=Date(Today()-8,'MM/DD/YYYY'))"},

Status={'1'}>}PreReleaseNo)

Make sure DateDimensionName and Date() has he same date format.

Regards,

Jagan.

Re: Last 7 days collection??

narender123 — Mon, 08 Jul 2013 12:32:41 GMT

Hi Mohit.

count(distinct {$<Year=,Month=,Datefield={">=$(=Date(Today()-8,'MM/DD/YYYY'))"},Status={'1'}>}PreReleaseNo)

this expression is working .Is it possible to create dynamic report.

if i select 4th of july 2013

then it will show the

Count of 28jun,29jun,30jun,1jun,2jun,3jun,4jun prerelease no.

its really appreciated.

Thanks

Re: Last 7 days collection??

narender123 — Mon, 08 Jul 2013 12:50:36 GMT

Thanks Tresesco,

So what i have to do.

if i requirement is that ,When i select a day like 4july 2013 then it will show

Count of 28jun,29jun,30jun,1jun,2jun,3jun,4jun prerelease no.

means dynamic result .Give me some suggestion please.

Thanks.

Re: Last 7 days collection??

narender123 — Mon, 08 Jul 2013 12:51:17 GMT

Thanks Jagan,

So what i have to do.

if i requirement is that ,When i select a day like 4july 2013 then it will show

Count of 28jun,29jun,30jun,1jun,2jun,3jun,4jun prerelease no.

means dynamic result .

Advice please.

Thanks.

Re: Last 7 days collection??

tresB — Mon, 08 Jul 2013 13:13:53 GMT

Use a variable :

vMaxSeven= Max(Date)-7

Then use this variable in the SET something like:

count({<Year=,Month=,Date={'>=$(vMaxSeven))'},Status={'1'}>}PreReleaseNo)

Again you have to be sure of the date format (numeric/string/date).... i guess the logic here would help you.

Re: Last 7 days collection??

AbhijitBansode — Mon, 08 Jul 2013 13:44:00 GMT

What about generating a unique ID for date field say DateID and with below expression:

Count({<DateId= {">=$(=Max(DateId) - 7)<=$(=Max(DateId))"}>} [Prerelease no])

Re: Last 7 days collection??

narender123 — Tue, 09 Jul 2013 14:51:17 GMT

Thanks to all.

Its really Appreciated.

Narender