Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi Atish,
I have created attached qvf according to your requirement.
Hope this helps.
Regards
Anvesh
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
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
Hi Atish,
I have created attached qvf according to your requirement.
Hope this helps.
Regards
Anvesh
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