topic Re: Display last 31 days data in QlikView

Display last 31 days data

Anonymous — Tue, 11 Aug 2015 11:48:24 GMT

Hello experts,

I have chart in that one date dimension and multiple expressions are there. I want to display last 31 days from max of date.

Can any one suggest me how to do that one.

Regards,

Kumar

Re: Display last 31 days data

lironbaram — Tue, 11 Aug 2015 11:52:57 GMT

in yout expressions

use this set analysis

sum({<Date={"$(=max(Date)-30)"}>} Sales)

use the same set analysis on all expressions

Re: Display last 31 days data

Anonymous — Tue, 11 Aug 2015 11:53:55 GMT

Hi,

Try this:

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=Date(Today()-30))<=$(=Today())’}>} Your expression )

Re: Display last 31 days data

Anonymous — Tue, 11 Aug 2015 11:57:26 GMT

Hi,

I have expression like this

=count(DISTINCT{$<[Site Visit Hit Source]={"<>5,7,8,9"}, [Site Visit Post Page Event] = {0}>} [Visitor ID])

can you guide me how to include this one in that particular expression.

Regards,

Kumar

Re: Display last 31 days data

Anonymous — Tue, 11 Aug 2015 11:58:06 GMT

Sum({<Year=, Quarter=, Month=, Week=, Date={"$(=max(Date)-30)"}>} Sales)

Re: Display last 31 days data

maxgro — Tue, 11 Aug 2015 12:03:11 GMT

if the date field is Date

=count(DISTINCT{$<[Site Visit Hit Source]={"<>5,7,8,9"}, [Site Visit Post Page Event] = {0}, Date={"$(=max(Date)-30)"}>} [Visitor ID])

=count(DISTINCT{$<[Site Visit Hit Source]={"<>5,7,8,9"}, [Site Visit Post Page Event] = {0}, Date={"$(=date(max(Date)-30))"}>} [Visitor ID])

Re: Display last 31 days data

Anonymous — Tue, 11 Aug 2015 12:55:56 GMT

I have written same logic, but it is displaying more than 31 days data.

Pls screen the screen shot.

In above date max date is 21/02/2015, but I am seeing even 2014 data also. Could you help me on this issue

Regards,

Kumar.

Re: Display last 31 days data

maxgro — Tue, 11 Aug 2015 13:11:05 GMT

could you post your Qlik doc or an extract?

or if you can't

start simple

=count(DISTINCT{$<Date={"$(=date(max(Date)-30))"}>} [Visitor ID])

remove the label Unique Visitors, go with the mouse on the blank label, capture the expression (image) and post

EDIT

this is not correct

[Site Visit Hit Source]={"<>5,7,8,9"}

replace with

[Site Visit Hit Source]=-{5,7,8,9}

Re: Display last 31 days data

Anonymous — Tue, 11 Aug 2015 13:32:10 GMT

Hi,

=count(DISTINCT{$<[Site Visit Hit Source]={"<>5,7,8,9"}, [Site Visit Post Page Event] = {0},[Time Full Date]={"$(=max(Date([Time Full Date],'MM/DD/YYYY'))-30)"}>} [Visitor ID])

There was mistake in that, instead of [Time Full Date] i took date only.

After including this [Time Full Date]={"$(=max(Date([Time Full Date],'MM/DD/YYYY'))-30)" one in set analysis it is giving 0's. I don't know why it is happening. It should give last 31 days data?

Regards,

Kumar

Re: Display last 31 days data

qlikviewwizard — Wed, 12 Aug 2015 06:22:37 GMT

HI kumarreddy257

Did you resolve the issue? Thank You.

Re: Display last 31 days data

maxgro — Wed, 12 Aug 2015 08:07:42 GMT

count( {$<

[Site Visit Hit Source]=-{5,7,8,9},

[Site Visit Post Page Event] = {0},

[Time Full Date]={">=$(=date(max([Time Full Date])-30, 'MM/DD/YYYY'))"}

>} DISTINCT [Visitor ID])

the order of the function is important

max([Time Full Date]) this is a num

max([Time Full Date])-30 this is a num

date(max([Time Full Date])-30, 'MM/DD/YYYY') this is a MM/DD/YYYY

and if [Time Full Date] is a MM/DD/YYYY it should be ok

Re: Display last 31 days data

Anonymous — Wed, 12 Aug 2015 08:52:18 GMT

No, still i am struggling with this issue.

Re: Display last 31 days data

sasiparupudi1 — Wed, 12 Aug 2015 14:14:38 GMT

what is the format of the value in [Time Full Date] ?

and if you put date(max([Time Full Date])-30, 'MM/DD/YYYY') in a text box, do you get the date in the format that matched to Time Full Date?

Re: Display last 31 days data

ganeshreddy — Wed, 12 Aug 2015 14:42:56 GMT

Hi Kumar,

Try this it gives you last month count from today.

count(if(Time Full Date >= AddMonths(today(),-1),[Visitor ID])) as Last_ Month_VisitorID_Count,

Cheers,

Ganesh

Re: Display last 31 days data

Anonymous — Wed, 12 Aug 2015 16:34:45 GMT

Hi Ganesh,

I should not take today(), because based on the data load I have to take it.

Regards,

Kumar

Re: Display last 31 days data

Anonymous — Wed, 12 Aug 2015 16:38:22 GMT

I got date. Some where it is going wrong. I am new to qlikview and can't figure out exactly.

Re: Display last 31 days data

Anonymous — Wed, 12 Aug 2015 16:41:47 GMT

[Time Full Date ] is time stamp.

Re: Display last 31 days data

maxgro — Wed, 12 Aug 2015 21:50:36 GMT

try with a date

to get a date from timestamp, in script you can add a field

.....

date(floor([Time Full Date])) as [Full Date]

.....

Re: Display last 31 days data

qlikviewwizard — Thu, 13 Aug 2015 01:59:44 GMT

Can you attach the sample data to demonstrate your issue. It will help the folks to give correct answer instead of guessing answers. Thank you.

Re: Display last 31 days data

jagan — Thu, 13 Aug 2015 02:31:35 GMT

Hi,

I think the issue is the Date format in your set analysis expression, in script change like below

LOAD

Date(Floor(DateFieldName)) AS DateOnly

FROM DataSource;

Now use below expression

=Count( {<[Site Visit Hit Source]=-{5,7,8,9},

[Site Visit Post Page Event] = {0},

DateOnly={">=$(=Date(Max([DateOnly])-30))<=$(=Date(Max(DateOnly)))"}

>} DISTINCT [Visitor ID])

Hope this helps you.

Regards,

Jagan.