Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Want to Display all years irrespective of data present

I have OrderHeader table And Budget table

I modify budget table using crosstable as

Budger_CrossTable.PNG

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)

Req1.PNG

But Actual Out Put Coming as

Actual OutPut.PNG

My Table View is

Table.PNG

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

Thank You.

7 Replies
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.


talk is cheap, supply exceeds demand
Not applicable
Author

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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


talk is cheap, supply exceeds demand
Not applicable
Author

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

Not applicable
Author

Thank You Adam,

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