topic Re: Chart with Dimension of "Today", "current month" and "current month of last year" in QlikView

Chart with Dimension of "Today", "current month" and "current month of last year"

Facundo — Wed, 16 Nov 2016 19:34:42 GMT

Hi!,

I need to create a chart to display :

Expressions :

SUM(Quantity)

SUM(Sales)

One Dimension:

"Today", "Current Month" and "Current Month of Last Year".

I have a Date column.

Example :

How can i achieve this?

Thanks!

Re: Chart with Dimension of "Today", "current month" and "current month of last year"

sunny_talwar — Wed, 16 Nov 2016 19:42:04 GMT

You can use ValueList() function to create a synthetic dimension and then use Pick(Match()) to assign values using set analysis to the three different dimension or you can use an Island Table approach and again use Pick(Match()) to assign value to each of the three components of your dimension

Re: Chart with Dimension of "Today", "current month" and "current month of last year"

Facundo — Wed, 16 Nov 2016 19:46:10 GMT

Thanks for the quick answer, can you give me an example? because I tried but failed.

Re: Chart with Dimension of "Today", "current month" and "current month of last year"

sunny_talwar — Wed, 16 Nov 2016 19:56:07 GMT

Check this out

Re: Chart with Dimension of "Today", "current month" and "current month of last year"

vinieme12 — Wed, 16 Nov 2016 19:57:25 GMT

Add a flag for today in the below and your set expression will be a lot more simpler

Period Presets: Compare Periods on the fly

Re: Chart with Dimension of "Today", "current month" and "current month of last year"

Facundo — Thu, 17 Nov 2016 15:03:15 GMT

Thanks!, I think this is the way to go, but can't make it work if I replace "Today" for max selected date on the calculated dimension, I recieve an "Error on calculated dimdension".

I replaced this :

=Pick(Dim,

Date(Today(), 'DD-MM-YYYY'),

Date(MonthStart(Today()), 'MMM-YY'),

Date(MonthStart(Today(), -1), 'MMM-YY'))

For this :

=Pick(Dim,

Date(MAX(Date), 'DD-MM-YYYY'),

Date(MonthStart(MAX(Date)), 'MMM-YY'),

Date(MonthStart(MAX(Date), -1), 'MMM-YY'))

Re: Chart with Dimension of "Today", "current month" and "current month of last year"

sunny_talwar — Thu, 17 Nov 2016 15:04:33 GMT

Try this:

=Pick(Dim,

Date(MAX(TOTAL Date), 'DD-MM-YYYY'),

Date(MonthStart(MAX(Date)), 'MMM-YY'),

Date(MonthStart(MAX(Date), -1), 'MMM-YY'))

Re: Chart with Dimension of "Today", "current month" and "current month of last year"

Facundo — Thu, 17 Nov 2016 18:03:38 GMT

Thanks again!; it's working, but don't know why, when the first date of the month is selected, it doesn't work.

Check attached qvw.

Re: Chart with Dimension of "Today", "current month" and "current month of last year"

sunny_talwar — Thu, 17 Nov 2016 18:56:54 GMT

The attached should work now. But I think there are simpler ways to do this kind of stuff. I will revisit this in the evening with a better solution

Re: Chart with Dimension of "Today", "current month" and "current month of last year"

Facundo — Thu, 17 Nov 2016 19:56:05 GMT

Thanks again Sunny, it's working on the example, but when I try to replicate on my application, doesn't work.

I will wait for your evening solution.

Re: Chart with Dimension of "Today", "current month" and "current month of last year"

sunny_talwar — Fri, 18 Nov 2016 02:46:35 GMT

Here is another solution

Straight Table

Dimension

=Pick(Dim,

Dual(Date(Date), Date*100),

Date(MonthYear, 'MMM-YY'))

Expression

=Pick(Only({1} Dim),

Sum({<Year, Month, Day, Date = {"$(=Date(Max(Date)))"}, MonthYear>}Sales),

Sum({<Year, Month, Day, Date, MonthYear = {"$(='>=' & Date(MonthStart(Max(MonthYear), -1), 'MMM-YY') & '<=' & Date(MonthEnd(Max(Date)), 'MMM-YY'))"}>}Sales))

Here MonthYear is created in script like this

Table:

LOAD Date(MakeDate(2016, 1, 1) + RecNo() - 1) as Date,

Ceil(Rand() * 100) * 10 as Sales

AutoGenerate 366;

Dim:

LOAD * Inline [

Dim

];

Calendar:

LOAD

Date(Date,'MM/DD/YYYY') as Date,

Year(Date) as Year,

Month(Date) as Month,

Date(MonthStart(Date), 'MMM-YY') as MonthYear,

Num(Day(Date),'00') as Day

Resident Table;