topic Help with a date calculation in QlikView

Help with a date calculation

josephinetedesc — Tue, 26 Sep 2017 01:55:36 GMT

Hi all

I would like some help with this please:

I am in Chart properties - It is a line chart - works beautifully when I have:the dimension

=date(AppointmentDate, 'www dd/MM/yyyy')

But

as part of a dashboard I would like to amek the same chart just show a week of data, starting 7 days before today.

The expression below in the Enable conditional box does not work.

date(AppointmentDate) >= date(today()-3)

Thank you

Jo Tedesco

Re: Help with a date calculation

Anil_Babu_Samineni — Tue, 26 Sep 2017 02:00:37 GMT

Try to use same in expression as set analysis..

Re: Help with a date calculation

josephinetedesc — Tue, 26 Sep 2017 02:13:49 GMT

How do I write it in the expression using set analysis? I am unsure how to use quotes etc.

date(AppointmentDate) >= date(weekstart(today()))

sum({< AppointmentStatus = {'Completed'}>}sales

Re: Help with a date calculation

vishsaggi — Tue, 26 Sep 2017 02:32:10 GMT

Where you want to include this date? What is your actual date format of your AppointmentDate field.

May be like

= Sum(({< AppointmentStatus = {'Completed'} , AppointmentDate = {">= $(Date(WeekStart(Today() -7)))"} >} sales)

Re: Help with a date calculation

Anil_Babu_Samineni — Tue, 26 Sep 2017 02:33:23 GMT

Left hand part you can't use calculations. I would prefer in script like

Date(AppointmentDate) as AppointmentDate // Or may be name changed

Then, Set analysis should be

sum({< AppointmentStatus = {'Completed'}, AppointmentDate = {"=AppointmentDate >= date(weekstart(today()))"}>} sales)

To make more clear, You can do like below as well

If(date(AppointmentDate) >= date(weekstart(today())), 1,0) as Flag

Then analysis should be

sum({< AppointmentStatus = {'Completed'}, Flag = {1}>}sales)

Re: Help with a date calculation

josephinetedesc — Tue, 26 Sep 2017 03:04:37 GMT

Thank you both

I have used this formula:

=if(AppointmentDate>= date(weekstart(today()-3)),date(AppointmentDate))

I placed it in the

chart properties

Dimensions

Edit - Used Dimensions.

I would have liked to use it in the set analysis but too complicated - I might try later.

Thank you again

Re: Help with a date calculation

josephinetedesc — Tue, 26 Sep 2017 03:07:01 GMT

To make more clear, You can do like below as well

Where do you put this? Loading script or in the expression?

If(date(AppointmentDate) >= date(weekstart(today())), 1,0) as Flag

Re: Help with a date calculation

Anil_Babu_Samineni — Tue, 26 Sep 2017 03:07:33 GMT

In fact, I don't like to use calculated dimensions until unless needed and it kills the performance. If you read optimize document from community there you will will get front end optimize tips and first initiate point is the same "Don't use Calculated dimensions"

Re: Help with a date calculation

Anil_Babu_Samineni — Tue, 26 Sep 2017 03:07:51 GMT

Script

Re: Help with a date calculation

josephinetedesc — Tue, 26 Sep 2017 03:40:05 GMT

Sorry Anil – I am not sure what has happened – I meant to mark your answer as correct, but I hit the wrong button! Too much multi tasking. Will you be able to change it? Or give me instructions how I can change it?

Re: Help with a date calculation

Anil_Babu_Samineni — Tue, 26 Sep 2017 03:56:08 GMT

NP, Let it be. But, future

1) Your response got it as mark correct

2) You have to UN-Mark from correct answer first(Ref image attaached) - It's under your correct response

3) Then, Mark it as proper flag ..

Re: Help with a date calculation

josephinetedesc — Tue, 26 Sep 2017 03:58:06 GMT

Can I do this? Because I marked it as correct – I don’t think I can change it … I wanted to mark your answer as correct – instead I did it to my reply!

Re: Help with a date calculation

Anil_Babu_Samineni — Tue, 26 Sep 2017 03:59:58 GMT

You can, Because you are in your control (Credentials is yours)