topic Re: Chart - Month/Year to date in QlikView

Chart - Month/Year to date

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

How do I make my chart only show data up until todays date for previouse years?

I have tried doing it as an Expresstion like this:

Count ({<YEAR={$(vPreviousYear-1)},MONTH={'<=$(vMonth)'},DATE={'<=$(vToday)'}>}DATA)

But the result it the date is set as max in very month. So today it will only show up until todays date in all the present month also.

But I what the full data shown in the past months, and only month-to-date i present month.

Re: Chart - Month/Year to date

jmvilaplanap — Tue, 11 Jul 2017 15:46:13 GMT

I understand that for explample if today is 11/07/2017 you want to show data from 01/01/2016 to 11/07/2016.

Then, change DATE for DAY (if you have this field).

Regards

Re: Chart - Month/Year to date

Anil_Babu_Samineni — Tue, 11 Jul 2017 16:07:27 GMT

Will you provide variables which you used??

Re: Chart - Month/Year to date

sergio0592 — Tue, 11 Jul 2017 20:31:12 GMT

Hi,

For current year, from 1er of month to today :

=Count({<Year, Month,Date={">=$(=MonthStart(today())) <=$(=today())"}>} Data)

For previous year, from 1er of month to today :

=Count({<Year,Month,Date={">=$(=MonthStart(today(),-12)) <=$(=AddYears(today(),-1))"}>} Data)

Hope it helps.

Re: Chart - Month/Year to date

Thu, 13 Jul 2017 09:39:57 GMT

Hi Jose,

Thanks for your reply.

When I try your solution, I'll get a blank chart.

Here are what I tried:

Count ({<YEAR={$(vPreviousYear)},MONTH={'<=$(vMonth)'},DAY={'<=$(vToday)'}>}DATA)

Re: Chart - Month/Year to date

Thu, 13 Jul 2017 09:42:43 GMT

Hi Anil,

Here are the variables I used:

vPreviousYear) =year(today())-1

vMonth =month(today())

vToday =date(Today())

vTodayPreviousYear) =date(monthstart(today())-365,'DD-MM-YYYY')

Re: Chart - Month/Year to date

Thu, 13 Jul 2017 09:50:20 GMT

Jean-Baptiste RENAULT skrev:

Hi,

or previous year, from 1er of month to today :

=Count({<Year,Month,Date={">=$(=MonthStart(today(),-12)) <=$(=AddYears(today(),-1))"}>} Data)

Hope it helps.

Hi Jean-Baptiste,

When I try this, I get a blank result. Why could that be?

Re: Chart - Month/Year to date

jmvilaplanap — Thu, 13 Jul 2017 10:06:51 GMT

Hi mortenrostved

What's the value of $(vToday)? Is needed a day not a date, because you are comparing days. Maybe the error is there.

If is a date, you can replace it with $(=day(today()))

Regards

Re: Chart - Month/Year to date

Thu, 13 Jul 2017 10:29:23 GMT

The value of vToday =date(Today())

Then I try your solution with $(=day(today())), I getting data in the chart, but as I can see, I'm geting the result of the day, and not as I need: the date. I need to get the full month of the previouse month, and in the present month, I only want the data from the 1th of the month until today (ex 12th july).

Did I make my self more clear now?

Re: Chart - Month/Year to date

sergio0592 — Thu, 13 Jul 2017 10:44:50 GMT

Have you tried with the formula for current year? Does it works for you?

=Count({<Year, Month,Date={">=$(=MonthStart(today())) <=$(=today())"}>} Data)

You can try with :

=Count({<Year,Month,Date={">=$(=MonthStart(date(today()),-12)) <=$(=AddYears(date(today()),-1))"}>} Data)

Re: Chart - Month/Year to date

jmvilaplanap — Thu, 13 Jul 2017 10:45:23 GMT

I going to try to explain it without variables

Try with this, this must to work

Count ({<YEAR={$(=Year(Today()))},MONTH={'$(=Month(Today()))'},DAY={"<=$(=Day(Today()))"}>}DATA)

Re: Chart - Month/Year to date

Thu, 13 Jul 2017 11:15:52 GMT

This are getting me the result of the 1th until today in every month. How do I get the full month in all the previouse months?

Re: Chart - Month/Year to date

jmvilaplanap — Thu, 13 Jul 2017 11:19:51 GMT

With this you will have a "Year to Date"

Count ({<YEAR={$(=Year(Today()))},DATE={"<=$(=(Today())"}>}DATA)