Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Not able to restrict 31 days data

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

26 Replies
kaushik_solanki
Not applicable

Re: Not able to restrict 31 days data

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

jagan
Not applicable

Re: Not able to restrict 31 days data

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.

Not applicable

Re: Not able to restrict 31 days data

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

kaushik_solanki
Not applicable

Re: Not able to restrict 31 days data

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

sunny_talwar
Not applicable

Re: Not able to restrict 31 days data

Try this may be:

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

jagan
Not applicable

Re: Not able to restrict 31 days data

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.

Not applicable

Re: Not able to restrict 31 days data

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

kaushik_solanki
Not applicable

Re: Not able to restrict 31 days data

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

Not applicable

Re: Not able to restrict 31 days data

Hi Kaushik,

Thanks for ur patience and replying to my queries.

Can u look into this my actual Date.