Want to Display all years irrespective of data present

[#1]

I have OrderHeader table And Budget table

I modify budget table using crosstable as

I have Sales value till Sep 2014 Present in Order Detail Table which is link with orderHeader Table through OrderID

and i link budget with orderHeader with Composite key as OrderID_MonthYear

when i select 2014 table shrink to Sep 2014 but i want till Apr 2014 to Mar 2015 to be display..

Require Output is: (only instead of Customer replace by OrderId)

But Actual Out Put Coming as

My Table View is

Plz send me Script File as well as QVW File Coz i am Beginner and Using Personal Edition

Thank You.

[Gysbert_Wassenaar]

Create your Budget_Month field as a date instead of a text string:

Date(Date# (Capitalize(B_Month)&if(ApplyMap('Month',Capitalize(B_Month))>9,'2015','2014'),'MMMYYYY'),'MMMYYYY') as Budget_Month

Then change your chart expressions to:

=sum({<Budget_Month={'<=$(=max(Budget_Month))'}>}Sales)

=sum({<Budget_Month={'<=$(=max(Budget_Month))'}>}Budget_Amount)

And set the sort order of the Budget_Month field to Sort By Numeric Value Ascending.

[#3]

Thanks for the help Gysbert Wassenaar

i change my Budget_Month field and Expression in pivot table as you mention

but not giving any output in pivot table

[Gysbert_Wassenaar]

Works for me. See attached qvw.

[#5]

Hi.....Gysbert Wassenaar

I saw Your QVW file. It Shows only till Sep 2014 but i want all Apr 2014 to Jan 2015 fiscal year.

Sales is only till Sep 2014 but we have Budget_amount  till Jan 2015.

So, i want Apr 2014 to Jan 2015 but after Sep 2014 it should only display budget_Amount value and sales as Null or Zero.

I want above condition to be true after selecting 2014 fiscalYear

[Gysbert_Wassenaar]

Try clearing the selections

[#7]

by default, the dates available from your data model will be those dates where transactions exist.  If you want to build a calendar that contain a range of dates irregardles of the data, you will need to build what is called a master calendar - you basically define a min date and max date and then have all of the dates inbetween generated independent of data

please refer to the link below - there is a response by Sunny T that does an excellent job of explaining and has a good example

Use of Master Calendar

[#8]

Thank You Adam,

I got My output after implementing right calendar and Concatenation of OrderHeader and Budget Table.