Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
jagan
Luminary Alumni
Luminary Alumni

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
Author

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sunny_talwar

Try this may be:

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

jagan
Luminary Alumni
Luminary Alumni

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
Author

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

Hi Kaushik,

Thanks for ur patience and replying to my queries.

Can u look into this my actual Date.