subset of date

[cedriclupo]

Hello Community,

I am developing a large application and i'm facing a serious problem.

I have dates in a calendar going from 2018 to 2007.

I show my order quantity as experssion, over the monthYear dimension.

My customer want to see from today+2years and today -2years. And no missing value where there is no order during one month.

Is this possible?

i tried with set analysis, it doesn't calculate but still shows all the months.

Thank you

[VishalWaghole]

Hi cedric,

Please share your sample Data that will give more clarity.

- Regards,

Vishal Waghole

[Anonymous]

I recommend to create an additional MonthYear field that includes only the date range you need, and use it in the charts.  It take very little of RAM, and makes your calculations simpler.

[cedriclupo]

Hello guys and thank you for your answer.

I don't thinq i would make another calendar aside from the master calendar to access those dates . They are already available in the calendar and wish to make a subset of it. However if no other solution i'll keep that in mind

To make it simpler, i uploaded (as suggested) a sample to help understand my concern. I'm sure i'm not the only person facing this problem and there must be a solution.

Thank you a lot for your answers

cedric

[Anonymous]

Cedric

For your Pivot Table Missing Months have you tried on the Presentation tab unticking the Suppress Zero-Values option ?

Best Regards,     Bill

[cedriclupo]

Bill,

when i do this it works ok, however i only want dates from current year -2 and +2 (2011-2014) but the calendar gives me dates from 2008 to 2018 (+5 -5).

For the moment i can only have one or the other:

->all dates until 2018

->Only month where there are values

[Anonymous]

Cedric,

I'm not suggesting to create another calendar - just an additional field to the existing calendar.  For example, you have a calendar 2007 to 2018 with the field Year, you can do this:

LEFT JOIN (Calendar) LOAD DISTINCT
Year,
Year as LimitedYear
RESIDENT Calendar
WHERE Year >= year(today())-2 and Year <= year(today())+2;

And, use this LimitedYear in the charts.
Certainly there are other ways, but this one makes your life easier, trust me...

Regards,
Michael

PS: The test.qvw is quite large, so I chose not to download.