Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
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
MVP & Luminary
MVP & Luminary

Re: Want to Display all years irrespective of data present

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

Re: Want to Display all years irrespective of data present

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

MVP & Luminary
MVP & Luminary

Re: Want to Display all years irrespective of data present

Works for me. See attached qvw.


talk is cheap, supply exceeds demand
Not applicable

Re: Want to Display all years irrespective of data present

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

MVP & Luminary
MVP & Luminary

Re: Want to Display all years irrespective of data present

Try clearing the selections


talk is cheap, supply exceeds demand
Not applicable

Re: Want to Display all years irrespective of data present

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

Re: Want to Display all years irrespective of data present

Thank You Adam,

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