Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jacintof
Contributor
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.

4 Replies
settu_periasamy
Master III
Master III

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

Capture.JPG

Check the attachment.

jacintof
Contributor
Contributor
Author

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.

settu_periasamy
Master III
Master III

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
Contributor
Contributor
Author

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 !