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
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:
And set the sort order of the Budget_Month field to Sort By Numeric Value Ascending.
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
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
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