There is any way to build Hierarchy in Qlikview like YEAR-->Quarter-->MONTH-->DATE (Like SSAS)

[#1]

Hi All,

I am using QlikView Desktop 32 Bit 11.2 Version.

I want to know that there is any way to build Hierarchy in Qlikview something like YEAR-->Quarter-->MONTH-->DATE (Same as SSAS).

Please provide script and step by step process and supportive docs if any

Thanks in advance.

[swuehl]

You need to create your calendar data yourself in the script, starting from e.g. a date field on your input data.

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar

Then you can use all calendar fields, Year, Quarter, Month, Date etc.

Then you may also use drill-down or cyclic groups (made of your calendar fields) as dimension.

[#3]

When it comes to calendar itself you can start here: community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar.

Later on you may want to create drill-down group on top of your calendar (Document Properties => Groups)

Lukasz

[iktrayanov]

You probably need to create a GROUP.

[#5]

Hi Swuehl/Lukasz,

I forgot to mention, I already have Date and Time Dimension in my DB and I am fetching data from there. Below are the Scripts.

DIM_DATE_DATA:

LOAD

TXN_DATE_KEY AS TXN_DATE_KEY,

DATE,

DATE_DESC,

DAYOFWEEK,

DAYNAMEOFWEEK,

DAYOFMONTH,

DAYOFYEAR,

WEEKOFYEAR,

MONTH,

MONTHOFYEAR,

LASTDAYOFMONTH_IND,

QUARTER,

YEAR,

HOLIDAY_IND

FROM DIM_DATE_DATA.QVD (qvd);

DIM_TIME_DATA:

LOAD

TXN_TIME_KEY AS TXN_TIME_KEY,

TIMEFULL,

TIMEHOUR,

TIMEMINUTE,

TIMESECOND,

BUSYTIME,

AMPM,

SECONDS

FROM DIM_TIME_DATA.QVD (qvd);

I would like to same code for Creating Hierarchy using above query. Please guide if possible modify above query for better understanding.

[swuehl · Accepted solution]

Have you tried to create groups from your fields? You can create groups from document properties -> groups or from any dimension tab.

Hierarchic Groups (Drill-Down)

When several fields form a natural hierarchy, it makes sense to create a drill-down group. Typical examples of hierarchic groups are:

Time: Year, Quarter, Month

or:

Geography: Continent, Country, State, City

When a drill-down group is used as a dimension in a chart, the chart will use the first Fields in the group's list of fields that has more than one possible value. If selections are made that cause the field to have only one possible value, the next field in the list will be used instead, provided that it has more than one possible value. If no field in the list has more than one possible value, the last field will be used anyway.

In the first example above, Year will be used as chart dimension until a single year is selected. The chart will then show Quarter. If a single quarter is selected, the chart will switch to Month.

As selections disappear, so that more than one field becomes available in the upper fields of the group's field list, the chart will automatically be drilled back up. Forced drill-up can be achieved by clicking on the drill-up icon in the chart.

A special feature appears when the same drill-down group is used in more than one dimension in the chart specification. The second time the group occurs, the field used will automatically be taken from one step down the group's field list. For example, if you create a two-dimensional chart with the group Geography above used as both Main dimension and 2nd dimension, Continent and Country will initially be used. As soon as one single continent is selected, Country and State will be used instead.

Note!
Although it is convenient to use this feature with natural hierarchies, there is nothing preventing its use in other situations as well.

[#7]

Hi All,

It worked fine.

I followed below steps:

Settings-->Document Properties-->Groups: Gave Group Name as "DATE_HIERACHY" and selected 4 columns (YEAR, QUARTER, MONTH, DATE) from DIM_DATE_DATA and by default it was selected Drill-Down Group so I left it as it.

Crated a Chart and Selected DATE_HIERACHY in Dimension and =SUM(Salary).

Output: It was displaying sum of Salary for all available years--> If you click on a particular year, It was drilling down to corresponding Quarters-->same way If you Click on particular Quarter It was Drilling down to corresponding Months and same way for dates.

There is an UP ARROW, If you click It will Drill UP to next level.