topic Re: Help - writing expression for a monthly chart using date field in QlikView

Help - writing expression for a monthly chart using date field

divya_anand — Tue, 13 Dec 2016 12:29:04 GMT

Hi all,

Any suggestions on writing the expression for the below case that I would like to see on a Monthly bar chart (Month is the dimension)

ScheduledDate<=MonthEnd of every month

Jan: count of all tickets where scheduledDate <=Jan (including jan 2016,dec 2015, nov 2015 & so on)

Feb: count of all tickets where scheduledDate <=Feb (including feb 2016, jan 2016,dec 2015, nov 2015 & so on)

Mar: count of all tickets where scheduledDate <=Mar etc

Thank you in advance.

Re: Help - writing expression for a monthly chart using date field

neelamsaroha157 — Tue, 13 Dec 2016 19:41:18 GMT

Are you looking for something like this

Count({$<ScheduledDate={"<$(=MonthEnd(Max(Date)))"}>}tickets)

Re: Help - writing expression for a monthly chart using date field

divya_anand — Wed, 14 Dec 2016 04:49:35 GMT

Hi Neelam,

Thank you for the response.

The date field for the month(dimension) and the scheduled date are the same in my case. ie. the month(dimension) is nothing but month(ScheduledDate). So, given your expression I should actually be using

Count({$<ScheduledDate={"<$(=MonthEnd(Max(ScheduledDate)))"}>}tickets)

This ain't working. Do you have any suggestions for this case?

Re: Help - writing expression for a monthly chart using date field

tresB — Wed, 14 Dec 2016 05:16:29 GMT

Could you post a sample qvw and explain your expected output?

Re: Help - writing expression for a monthly chart using date field

Anonymous — Wed, 14 Dec 2016 05:31:45 GMT

Try like this...

expression as

=Count({<ScheduledDate={">=$(=ScheduledDate) <=$(=MonthEnd(Max(ScheduledDate)))"}>}tickets)

Re: Help - writing expression for a monthly chart using date field

divya_anand — Wed, 14 Dec 2016 05:35:48 GMT

Hi Tresesco,

Here is a picture of what I am working towards having-

Thank you for the response & Let me know in case you need more information.

Re: Help - writing expression for a monthly chart using date field

neelamsaroha157 — Wed, 14 Dec 2016 13:56:12 GMT

Take a look if this is what you are looking for.

Re: Help - writing expression for a monthly chart using date field

divya_anand — Thu, 15 Dec 2016 04:06:41 GMT

Hi Neelam,

This works perfect. Thanks a ton.