28 Replies Latest reply: Aug 14, 2015 10:24 PM by jagan mohan rao appala

# Display last 31 days data

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

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

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

Hi,

Try this:

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

• ###### Re: Display last 31 days data

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

• ###### Re: Display last 31 days data

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])

or

=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

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

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

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

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

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

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

[Time Full Date ] is time stamp.

• ###### Re: Display last 31 days data

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

As in the screenshot ur date format is DD/MM/YYYY

So try using this:

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

Hope it helps you!!

• ###### Re: Display last 31 days data

Did you resolve the issue? Thank You.

• ###### Re: Display last 31 days data

No, still i am struggling with this issue.

• ###### Re: Display last 31 days data

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

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

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

Hi,

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

*,

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.

• ###### Re: Display last 31 days data

Hi Jagan,

As u suggested i modified the script and it  is displaying data. But i am still seeing all data. But it is not restricting to 31 days.

• ###### Re: Display last 31 days data

If you attach the sample file then it would easier to help you.

Regards,

Jagan.

• ###### Re: Display last 31 days data

Hi,

Try to restrict the 31 days from Dimension Limits option from bar chart properties.

Choose show only first 31 days, and sort the dimension in descending order.

Warm Regards,

Karthikeyan.

• ###### Re: Display last 31 days data

Hi Kumar, you can use couple of approaches for this requirement.

1. SET Analysis

As per discussion, you have time stamp nn your date field. so please remove the time with floor function.

Floor(DateTimeField) AS DateField --> QV generate number from this function & you can convert the number into date.

Count( {<DateField={">=\$(=Max(DateField)-31)"}>} DISTINCT VisitorID)

Note: the Max function also return number format & you need to explicitly convert number format into your data format if date field not in the number format.

2. Dimension Limits

First on sort tab you need to sort the data based on your date dimension desc order. In the dimension limits tab Select check box for Restrict which values are displayed using the first expression. and show only first 31 values. the down side of this approach if some one change the sort order on UI it will change.

• ###### Re: Display last 31 days data

hi,

I am attaching qvd and model. Can anyone help me how to get it back only 31 days data only.

Thanks,

Kumar

• ###### Re: Display last 31 days data

Hi Kumar,

There is no matching records between the dim_visitor and dim_time tables, I think there is an issue with the key

field site_visit_start_datetime_key, check it in the script which is generating the QVD.

Hope this helps you..

Regards,

Jagan.