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

[bvssudhakar]

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

[bvssudhakar]

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

[sunny_talwar]

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

[bvssudhakar]

Hi Sunny,

here i attached sample excel sheet

[sunny_talwar · Accepted solution]

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])

[bvssudhakar]

Hi Sunny,

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

And Please help me for that master date calendar query also

[sunny_talwar]

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

[bvssudhakar]

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

[bvssudhakar]

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

[sunny_talwar]

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

[bvssudhakar]

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??