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: 
atish-singh
Partner - Contributor II
Partner - Contributor II

value for specific date

Hi,

 

I want to calculate users for specific selected dates  as per the selection made through datepicker (Qlik dashboard bundle). My source date field is in datetime format : YYYY/MM/DD hh:mm:ss e.g. "2018/12/23 15:56:00000"

My scenarios are mentioned below:

When user select a date , it should show [count(users) for selected date] - [count(users) for previous day of selected date]
Example:

if user selects 20th December, should show [count(user) for 20th dec] - [count(user) for 19th dec]

If user selects 20th-30th dec, should show : [count(user) for 20-30 dec]  - [count(user) for 9-19 dec]

Approach I already tried:

Converted datetime field in data editor: DATE(floor(datetime),'MM/DD/YYYY') AS [%KeyDate]

Created few variable as listed below:

NoofDays=(max([%KeyDate])-min([%KeyDate]))+1

PreviousMaxdDate=Date(max([%KeyDate])-$(NoofDays),'MM/DD/YYYY')

PreviousStartDate= date(min([%KeyDate])-$(NoofDays),'MM/DD/YYYY')

Created KPI for showing output:

count({<[%KeyDate]={">=$(PreviousStartDate)<=$(PreviousMaxdDate)"}>}user_id) 

However, its not working. Any help would be appreciated.

Labels (1)
1 Solution

Accepted Solutions
Anvesh
Contributor III
Contributor III

Hi Atish,

I have created attached qvf according to your requirement.

Hope this helps.

Regards

Anvesh

View solution in original post

4 Replies
JordyWegman
Partner - Master
Partner - Master

Hi Atish,

The best thing to do is always floor your dates while this reduces the risk of wrong date configurations.

So use: Floor([Your date]) 

The easiest thing to do would be to create variables for your selected dates (as you have showed, but with the floored dates). But with the calculation you need to add a comma: 

count({<[%KeyDate]={">=$(PreviousStartDate) , <=$(PreviousMaxdDate)"}>}user_id) 

Jordy

Climber

Work smarter, not harder
atish-singh
Partner - Contributor II
Partner - Contributor II
Author

Hi Jordy,

 

I tried the way you suggested to floor all dates. However, this is also not working.

I also tried converting all date to numbers and was successful in calculating previous time period dates from current selection. The count of user is showing in the app but count of users for previous time period is not coming correctly.

Please find the attached qvf file. Please help me out.

 

Regards,

Atish Singh

Anvesh
Contributor III
Contributor III

Hi Atish,

I have created attached qvf according to your requirement.

Hope this helps.

Regards

Anvesh

atish-singh
Partner - Contributor II
Partner - Contributor II
Author

Hi Anvesh,

 Thanks a lot for the time and efforts you spent on providing the solution.

The solution provided by you is working appropriately.

Regards,

Atish Singh