topic Re: How to calculate date difference from one date to 30 days before Todays date in QlikView

How to calculate date difference from one date to 30 days before Todays date

bvssudhakar — Wed, 25 Nov 2020 16:16:04 GMT

Hello Experts,

Can you help me for this query..:=Sum({<Floor(Today()-30)-Floor([Closed date])>}[No. of CV Submitted])

I wrote this expression for bar chart but it is giving error

My requirement is i want to calculate date difference from closed date to last 30 days for resumes submitted column

Please help me out from this

swuehl stalwar1‌mbaeyens‌ jagan

Thank you in advance

Re: How to calculate date difference from one date to 30 days before Todays date

sunny_talwar — Thu, 09 Aug 2018 10:54:44 GMT

Are you looking for date difference or are you counting/summing the number of cv submitted here? What is the goal?

Re: How to calculate date difference from one date to 30 days before Todays date

bvssudhakar — Thu, 09 Aug 2018 11:17:01 GMT

Hi Sunny

I want to show how many resumes submitted between closed date column to 30 days before today

Re: How to calculate date difference from one date to 30 days before Todays date

sunny_talwar — Thu, 09 Aug 2018 11:18:45 GMT

In that case... try this

=Sum({<[Closed date] = {"$(='>=' & Date(Today()-30))"}>} [No. of CV Submitted])

Re: How to calculate date difference from one date to 30 days before Todays date

bvssudhakar — Thu, 09 Aug 2018 11:20:05 GMT

that means i want to sum of resumes submitted between those two dates

Re: How to calculate date difference from one date to 30 days before Todays date

Anil_Babu_Samineni — Thu, 09 Aug 2018 11:22:19 GMT

I think, This is what you are stating. Can you check this?

=Sum({<[Closed date] = {">=$(=Date(Max([Closed date])-30))<=Max([Closed date])"}>} [No. of CV Submitted])

Re: How to calculate date difference from one date to 30 days before Todays date

bvssudhakar — Thu, 09 Aug 2018 11:23:14 GMT

It's not giving any data, it is showing no records to display

Re: How to calculate date difference from one date to 30 days before Todays date

sunny_talwar — Thu, 09 Aug 2018 11:24:00 GMT

What is the format for Closed date? Is it a date or timestamp field?

Re: How to calculate date difference from one date to 30 days before Todays date

bvssudhakar — Thu, 09 Aug 2018 11:27:06 GMT

Hi Anil,

This is also not showing any result, I want to show the sum of resumes submitted between closed date column to 30 days before today's date

Re: How to calculate date difference from one date to 30 days before Todays date

bvssudhakar — Thu, 09 Aug 2018 11:29:24 GMT

I have taken the data from sql server and i think that column data type is varchar

Re: How to calculate date difference from one date to 30 days before Todays date

bvssudhakar — Thu, 09 Aug 2018 11:36:21 GMT

Hi Sunny,

That date column is varchar data type and in our qvw it is showing like as 01-jun-2017.

Is there any way to change that format in qlikview

Re: How to calculate date difference from one date to 30 days before Todays date

sunny_talwar — Thu, 09 Aug 2018 11:36:37 GMT

It might help if you can share a sample or sample data to show what you have?

Re: How to calculate date difference from one date to 30 days before Todays date

bvssudhakar — Thu, 09 Aug 2018 11:43:11 GMT

Hi Sunny,

here i attached sample excel sheet

Re: How to calculate date difference from one date to 30 days before Todays date

sunny_talwar — Thu, 09 Aug 2018 11:48:34 GMT

Does 134 looks like the number you would get from the sample file based on the condition you need?

Script

Table:

LOAD RecruiterName,

Skillname,

Date(Date#([Closed date], 'DD-MMM-YYYY')) as [Closed date],

"[No. of CV Submitted]" as [No. of CV Submitted],

Placed

FROM

[..\..\Downloads\CH09_20180809_171052.xls]

(biff, embedded labels, table is Sheet1$);

Basically, your date was read as a string by Qlik and that is why I used Date#() function to help interpret it as a date field....

and then used this

=Sum({<[Closed date] = {"$(='>=' & Date(Today()-30))"}>} [No. of CV Submitted])

Re: How to calculate date difference from one date to 30 days before Todays date

bvssudhakar — Thu, 09 Aug 2018 12:02:29 GMT

Hi Sunny,

Thank you very much for your help. It's working now.

And Please help me for that master date calendar query also

Re: How to calculate date difference from one date to 30 days before Todays date

sunny_talwar — Thu, 09 Aug 2018 12:06:09 GMT

which query? I am not sure I understand what you mean?

Re: How to calculate date difference from one date to 30 days before Todays date

bvssudhakar — Thu, 09 Aug 2018 12:07:06 GMT

Yeah in that post i am mentioning you now with sample data

Re: How to calculate date difference from one date to 30 days before Todays date

bvssudhakar — Thu, 09 Aug 2018 12:15:42 GMT

Hi Sunny,

I forgot ask one small suggestion, I have one list box of two open and close. In that listbox whatever i select this chart will show only closed state details only. for that how to modify our expression

Re: How to calculate date difference from one date to 30 days before Todays date

sunny_talwar — Thu, 09 Aug 2018 12:23:55 GMT

Not sure I understand? You have two list boxes... open and close... what are you selecting? and what needs to happen?

Re: How to calculate date difference from one date to 30 days before Todays date

bvssudhakar — Thu, 09 Aug 2018 12:31:13 GMT

I have only one listbox with open and close fields. if i select open field i want to change all charts except this chart (this remains only in closed selection only whatever field i select)....... I think this is possible

After Applying this....

and i have one more list box with billable and nonbillable fields, if apply this selection then only will apply changes in chart. Is this possible??