Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

How to show current and prior period values in one row? and able to drill down?

At first, my requirment is to present current and prior period booking data of hotel and the city where hotel belong to in one row.

I create a pivot table like table 1, Hotel Name and City Name are dimensions, and others are experssions.

Table1

Table1.png

My data model looks like image 1:

* A index table, provide fields to find hotels

* A hotel level and a city level aggregation table, both linked with index table

* A YearMonth table to select current period(one or more month), apply field OnSelect trigger to auto select YearMonth PY table when YearMonth is selected

Image 1

Image1.png

Here is definition of experssions:

Hotel Booking CY = sum({$<[YearMonth Hotel]=[YearMonth]>}[Hotel Bookings])

Hotel Booking PY = sum({$<[YearMonth Hotel]=[YearMonth PY]>}[Hotel Bookings])

City Booking CY = sum({$<[YearMonth City]=[YearMonth]>}[Hotel Bookings])

City Booking PY = sum({$<[YearMonth City]=[YearMonth PY]>}[Hotel Bookings])

and search string of the YearMonth trigger:

='('&concat(distinct date(AddMonths([YearMonth],-12),'MMM-YYYY'),'|')&')'

All of above works fine, but a littel change of requirment.

they need both table 1 and drill down table 1 through YearMonth, like table 2. It doesn't work if I just add YearMonth in the table, values in each row are summary of entire period, not break down by YearMonth.

Table2

Table2.png

Please help me solve the problem, many thanks! 

P.S.  Sorry for my terrible english.

9 Replies
Not applicable

Re: How to show current and prior period values in one row? and able to drill down?

Will somebody please help me

jonbrough
Valued Contributor

Re: How to show current and prior period values in one row? and able to drill down?

I think a sample application, or an example of the input data and the table1 output, will help to make your question clearer.

Jonathan

Not applicable

Re: How to show current and prior period values in one row? and able to drill down?

Hi Jonathan,

Thank you very much for your advices, please see attached.

a_mullick
Contributor II

Re: How to show current and prior period values in one row? and able to drill down?

Hi,

I think you should rework your data model. YearMonth should be shared dimension with the Hotel and City fact tables. There is no real need to have 'YearMonth City' and 'YearMonth Hotel' - they can both be named YearMonth and associate with the YearMonth time-dimension. 'YearMonth PY' could be calculated as an expression:

=Date(YearMonth-1, 'MMM-YYYY')

rather stored as an extra field?

Thanks,

Azam

jonbrough
Valued Contributor

Re: How to show current and prior period values in one row? and able to drill down?

I agree with Azam's comment below. The power of QlikView is in having data associated to each other.

You should not even need to have the aggregation tables. If data is recorded for a Hotel, because each Hotel is in a City, you should be able to aggregate by just changing the dimension.

See attached file. I used the data from the aggregation tables, so it is probably not accurate, but hopefully it shown what I mean. I have included various tables that aggregate against different dimensions.

Jonathan

Not applicable

Re: How to show current and prior period values in one row? and able to drill down?

Thanks for your reply, Azam and Jonathan.

I have to admit that my data model is a little bit strange.

But what I need in UI is a table that have hotel and city(where the hotel locate) booking in one row, both current and prior period, like table 1 shows. My client want to see hotel and city booking yoy%, and compare these two yoy%.

If underlaying table have these fields: Year, Month, Country Name, City Name, Hotel Name, Bookings(as Jonathan's example), is it posiable to make Table 1 and 2 out of it?

Azam, If I rename both 'YearMonth City' and 'YearMonth Hotel' as YearMonth, Qv detected loop while reloading, and Hotel aggr and Table 2 not right, please see attached.

jonbrough
Valued Contributor

Re: How to show current and prior period values in one row? and able to drill down?

Instead of just sum(HotelBookings) you can add logic in to the sum() function to limit th results to one particular year.

You can either do sum(if(Year='2013',HotelBookings)) or sum({<Year={'2013'}> HotelBookings}

Jonathan

Not applicable

Re: How to show current and prior period values in one row? and able to drill down?

finally, it works. The data model is still strange. please se attached for more detail.

while building HOTEL_INDEX, I made a Cartesian Product of Hotel and YearMonth, this process make sure a row exist for each Hotel in each YearMonth. Avoid error that, a Hotel don't have any booking in certain months, the City Booking of thoes months will missing too. Example of this kind of error is Holiday Inn in Feb-2013.

thanks everyone!

Highlighted
a_mullick
Contributor II

Re: How to show current and prior period values in one row? and able to drill down?

Hi,

I see you've got a solution. My apologies for the 'bad' asscociation I suggested whch resulted in a loop. I didn't think it through properly, but what I was thinking of was a single hotel dimension and a single fact table (city and name are both dimensions of a hotel and do not require seperate facts, you're just summing over different dimensions).

Your client may not be flexible about the presentation, but I found the idea of summing bookings over a city, when the hotel name is the lowest level dimension, somwhat confusing; making use of Year and Month as seperate dimensions also give you more flexibility. I've attached another example of what I meant with some alternative ideas for presenting the data.

Best wishes,

Azam

Community Browser