Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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.
Hi,
It is best to do this n the script, set analysis is slowing down the UI.
BR
Ariel
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 ....
BR
Ariel
Hi Ariel
Thank you for your reply.
What about set analysis? Would I have to do something for that?
Hi,
It is best to do this n the script, set analysis is slowing down the UI.
BR
Ariel