topic Re: Not able to restrict 31 days data in QlikView

Not able to restrict 31 days data

Tue, 25 Aug 2015 09:29:21 GMT

Hello experts,

I am new to Qlik view and

I am trying to restrict data for last 31 days data from max date. Actually date field is time stamp and converted that into date by floor function and used it in expression like below.

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

But it is giving complete data and not able to restrict 31 days data.

Regards,

Nanda

Re: Not able to restrict 31 days data

kaushiknsolanki — Tue, 25 Aug 2015 09:44:46 GMT

Hi,

Check the date formats.

Do one thing create the listbox of Date and see what format you have, try to get the ame format of the dates in set analysis.

It will solve your problem.

Regards,

Kaushik Solanki

Re: Not able to restrict 31 days data

jagan — Tue, 25 Aug 2015 10:04:22 GMT

Hi,

Check the date format

The DateOnly date format and Today() date format should be same then only it will work otherwise you need to format the date

=Count({<[DateOnly]={">=$(=Date(Max([DateOnly])-30, 'DD-MMM-YYYY'))<=$(=Date(Max([DateOnly]), 'DD-MMM-YYYY'))"} >} DISTINCT [Visitor ID])

Change DD-MMM-YYYY format to the DateOnly field format then expression works.

Regards,

Jagan.

Re: Not able to restrict 31 days data

Tue, 25 Aug 2015 10:12:44 GMT

Hi Kaushik,

Dateonly is giving as MM/DD/YYYY and this this field is coming from Date filed. Date field is time stamp and this date field used in dimensions as date([date],'MM/DD/YYYY') in table chart.

Pls find the attachment

Re: Not able to restrict 31 days data

kaushiknsolanki — Tue, 25 Aug 2015 10:18:57 GMT

Hi,

I guess your DateOnly field has one wrong, seems it is not showing correct Date.

First Check that.

Regarding the date format, you will have to use the date format in Set analysis to get the correct data. Refer to Jagan's Post about how to use it in set analysis.

Regards,

Kaushik Solanki

Re: Not able to restrict 31 days data

sunny_talwar — Tue, 25 Aug 2015 10:22:11 GMT

Try this may be:

=Count({<[DateOnly]={"$(='>=' & Date(Max([DateOnly])-30, 'MM/DD/YYYY') & '<=' & Date(Max([DateOnly]), 'MM/DD/YYYY'))"} >} DISTINCT [Visitor ID])

Re: Not able to restrict 31 days data

jagan — Tue, 25 Aug 2015 10:26:06 GMT

HI,

Try this expression

=Count({<[DateOnly]={">=$(=Date(Max([DateOnly])-30, 'M/D/YYYY'))<=$(=Date(Max([DateOnly]), 'M/D/YYYY'))"} >} DISTINCT [Visitor ID])

This should work, if not working then attach sample file.

Regards,

Jagan.

Re: Not able to restrict 31 days data

Tue, 25 Aug 2015 10:31:22 GMT

Hi Jagan,

Let me explain u here what I am trying to do that exactly

I have time stamp field date is there

In script I am converting it as date like

load

date(floor(date))

resident

time;

In table chart i (explicitly converting it into MM/DD/YYYY) used in dimension as date([date],'MM/DD/YYYY')

in expressions i used

Count( {<DateOnly={">=$(=Date(Max([DateOnly])-30))<=$(=Date(Max(DateOnly)))"} >} DISTINCT [Visitor ID]).

I am not able to figure out where it is going wrong.

Pls bare with me for while on this issue

Thanks&Regards,

Nanda

Re: Not able to restrict 31 days data

kaushiknsolanki — Tue, 25 Aug 2015 10:34:16 GMT

Dear Nanda,

Do not get confused between Date format in Dimension and Expression (Set Analysis).

In your case the format of the date in Dimension wont impact anything.

All you have to make sure is that while comparing dates in Set analysis, the format of the Date Field and the comparing expression should be same.

Regards,

Kaushik Solanki

Re: Not able to restrict 31 days data

Tue, 25 Aug 2015 10:42:20 GMT

Hi Kaushik,

Thanks for ur patience and replying to my queries.

Can u look into this my actual Date.

Re: Not able to restrict 31 days data

kaushiknsolanki — Tue, 25 Aug 2015 10:45:13 GMT

Hi,

This dates still contains the timestamp.

Use the Date(Floor(Date)) function to get only dates.

Regards,

Kaushik Solanki

Re: Not able to restrict 31 days data

Digvijay_Singh — Tue, 25 Aug 2015 11:01:14 GMT

These dates are in 1940s, hope you are purposely dealing with these old dates..

Re: Not able to restrict 31 days data

Tue, 25 Aug 2015 11:33:02 GMT

No. ETL team loaded from 1900 to 2020. Is it the issue.

Re: Not able to restrict 31 days data

Tue, 25 Aug 2015 11:35:55 GMT

Hi Kaushik,

I have converted into date already by using same script what u suggested and that field only DateOnly using in set analysis. I have given u screen shot as my original Date field is having these type of values.

Re: Not able to restrict 31 days data

kaushiknsolanki — Tue, 25 Aug 2015 11:40:21 GMT

Dear Nanda,

It would be great if you can share your application.

Regards,

Kaushik Solanki

Re: Not able to restrict 31 days data

Kushal_Chawda — Tue, 25 Aug 2015 11:51:41 GMT

try,

=Count( {<DateOnly={">=$(=Max(DateOnly)-30)<=$(=Max(DateOnly))"} >} DISTINCT [Visitor ID]).

Re: Not able to restrict 31 days data

Tue, 25 Aug 2015 11:57:22 GMT

No Use. My file size almost 400 MB or else i would have uploaded the file

Re: Not able to restrict 31 days data

sasiparupudi1 — Tue, 25 Aug 2015 12:01:44 GMT

the year on your dateonly filed is showing 1900? is it correct?

Re: Not able to restrict 31 days data

Tue, 25 Aug 2015 13:00:57 GMT

Hi,

I am attaching QVW doc and removing all possible values.

Regards,

Nanda

Re: Not able to restrict 31 days data

Tue, 25 Aug 2015 13:12:48 GMT

Hi,

Yes, it is correct. ETL people loaded dates from 1900 to 2020 with time stamps.