Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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
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
Try this may be:
=Count({<[DateOnly]={"$(='>=' & Date(Max([DateOnly])-30, 'MM/DD/YYYY') & '<=' & Date(Max([DateOnly]), 'MM/DD/YYYY'))"} >} DISTINCT [Visitor ID])
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.
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
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
Hi Kaushik,
Thanks for ur patience and replying to my queries.
Can u look into this my actual Date.