Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

jacintof
New Contributor

Chart real vs budget with canonical calendar.

Hi everyone,

I have the following data model:

File_Orders: this table contains the orders with date on which the order is received (date_in), date when the order request is delivered (date_out), amount of the sale made (Sales), etc.

Budget: this table contains if it is Budget or butget update (Budget_Type), if the Budget is relative to the orders received (Order_type=IN) o delivered  (Order_type=OUT) and the Year, Month and Budget Sales values.

I want a chart that when you select a month in the listbox (where the field of the listbox will be "MONTH FECHA CANONICA"), we will have two bar for month: one with the total of sales (DateType={'Date OUT'}) and other with de Budget_sales of each month (where Order_type=OUT).

I don't know how link Budget table with file_orders table. (maybe I need to use generic keys ? but I don't know how...)

Can someone help me ?

Thanks in advance.

Tags (1)
4 Replies

Re: Chart real vs budget with canonical calendar.

May be you can create one more field (Monthyear) in both the tables..(BUDGET and CALENDAR_CANONIC_

Capture.JPG

Check the attachment.

jacintof
New Contributor

Re: Chart real vs budget with canonical calendar.

Hi Settu,

thanks very much for your answer. This option works correctly !!!

A last question, please:

If now I add another listbox with Product (field "Product" from the table FILE_ORDERS). And detail the budget month, adding the product, f.ex.:

Load * Inline

[

    Budget_Type, Order_type, Year, Month, product, Budget_Sales

    BUP, OUT, 2016, Jan, Chair, 50

    BUP, OUT, 2016, Jan, Pen, 20

    BUP, OUT, 2016, Jan, Book, 20

    BUP, OUT, 2016, Feb, Chair, 10

    BUP, OUT, 2016, Feb, Pen, 40

    BUP, OUT, 2016, Feb, Book, 25

    BUP, OUT, 2016, Mar, Chair, 20

    BUP, OUT, 2016, Mar, Pen, 30

    BUP, OUT, 2016, Mar, Book, 15...]

Will works the same data model ?? I need when I filter for a certain product in the listbox, then on the chart display the sales of that product per month versus the budget for that product?

I've explained ?


Thanks you very much for the help !!!

Jacinto.

Re: Chart real vs budget with canonical calendar.

I don't think it will work. already you have the 'Product' field in your 'FILE_ORDERS' table, right? you can select it from there, correct?

jacintof
New Contributor

Re: Chart real vs budget with canonical calendar.

I have the "product" field in "FILE_ORDERS" table, but now I add also the product detail in Budget table. As I said in the last message the Budget table now will be:

BUDGET:

Load * Inline

[

    Budget_Type, Order_type, Year, Month, product, Budget_Sales, Monthyear

    BUP, OUT, 2016, Jan, Chair, 50, Jan2016

    BUP, OUT, 2016, Jan, Pen, 20, Jan2016

    BUP, OUT, 2016, Jan, Book, 20, Jan2016

    BUP, OUT, 2016, Feb, Chair, 10, Feb2016

    BUP, OUT, 2016, Feb, Pen, 40, Feb2016

    BUP, OUT, 2016, Feb, Book, 25, Feb2016

    BUP, OUT, 2016, Mar, Chair, 20, Mar2016

    BUP, OUT, 2016, Mar, Pen, 30, Mar2016

    BUP, OUT, 2016, Mar, Book, 15, Mar2016

...]

Can you tell me how I can add a simple file qw for you can see what I'm saying ?? (now I don't see how to attach a file)

Thanks !