topic Re: Creating monthly data with set analysis and date islands in QlikView

Creating monthly data with set analysis and date islands

Mon, 22 Sep 2014 11:09:06 GMT

THE PROBLEM

I want to be able to load some Sales data and some Purchase data from two separate tables which each have a date but show monthly data from each table based on the MasterCalendar.

So the Sales table has a Sales date and the Purchase table has a Purchase date. I have created a Master Calendar with all of the required calendar fields (CalendarDate, CalendarMonth etc)

The ideal situation is that when a user selects a date, QlikView will show the Sales data for that selected date and the Purchase data for that selected date. I also want the report to behave so that when a user selects a month from the CalendarMonth, Sales AND Purchase data are shown for the month.

EXAMPLE:

- The report has a bar chart with Sales AND Purchase data.

- The user selects 15/09/2014 from CalendarDate, the chart shows Sales and Purchase data for 15/09/2014 in the same chart.

- The user then selects September from CalendarMonth, the chart shows Sales and Purchase data for the month of September on the same chart.

I have created a chart with the following set analysis for both measures:

SUM({<SalesDate = {"$=(Date(CalendarDate))"}, CalendarMonth = , CalendarYear = }>Sales)

SUM({<PurchaseDate = {"$=(Date(CalendarDate))"}, CalendarMonth = , CalendarYear = }>Purchase)

When I select a date, I am getting the sales and purchase amount for that date only when I create two separate tables each for Sales and Purchase.

When I create one table to show both, I see nothing. When I select a CalendarMonth, I also see nothing.

How can I get to a solution where when I have one chart or table and a user selects either a date or month, the chart/table should show the Sales AND Purchase data for one date or one month when selected.

Re: Creating monthly data with set analysis and date islands

ariel_klien — Mon, 22 Sep 2014 11:18:29 GMT

Hi,

You need to create link table like this:

sale:

load *,

sale_date,

Sale_num&'|'sale_date as key_Sale

from sale.qvd;

Purchase:

load *,

Purchase_date,

Purchase_num&'|'Purchase_Date as key_Purchase

from sale.qvd;

link_table:

load distinct

sale_date as general_date,

key_Sale

Resident sale;

join

load distinct

Purchase_date as general_date,

key_Purchase

Resident Purchase;

Calendar:

load date as general_date,

...

form ....

Ariel

Re: Creating monthly data with set analysis and date islands

Tue, 23 Sep 2014 06:53:44 GMT

Hi Ariel

Thank you for your reply.

What about set analysis? Would I have to do something for that?

Re: Creating monthly data with set analysis and date islands

ariel_klien — Tue, 23 Sep 2014 06:58:54 GMT

Hi,

It is best to do this n the script, set analysis is slowing down the UI.

Ariel