Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
qvraj123
Contributor II

Need help - one to many -two metrics - actual vs budget - two different sources


Hi All,

Thanks for your time, Could anyone please suggest the best approach for the following scenario

TABLE 1 - This source file includes the data for expenses or actual for a given chargeID for each month, each employee, each department, each cost category etc - we are only interested in showing the expense amount for the following

Department, Cost Category, Cost Element, Month - for now we want to look at these only and the sum of expense

**Many rows for a given ChargeID

TABLE 2- This source includes the data for the budget at the chargeID level and includes Department, Node, Project Owner as other attributes and the budget amount - so when we look at the

Department level - budget is the sum of all the chargeID's

Node - budget is the sum of all the ChargeID's

Project - budget is the sum of all the ChargeID's

* One row for a given ChargeID

If it was like a reference table I can use APPLYMAP function and get the descrtion field (something like this) - but here I have the metric or fact in my other table

could anyone please suggest a good way of handling this - attached data

thanks a lot for your time

Rj

1 Solution

Accepted Solutions
Digvijay_Singh
Honored Contributor III

Re: Need help - one to many -two metrics - actual vs budget - two different sources

See, if this can help to get you started.

6 Replies
qvraj123
Contributor II

Re: Need help - one to many -two metrics - actual vs budget - two different sources

Thanks for your time, could anyone please suggest a best approach for this kind of datasets

interesting views

By Department - department exists in TABLE1

   Department   actual$         Budget$

By Project - project exists in TABLE2

Project          actual$        Budget$

By Node - Node exists in TABLE1

Node            actual$         Budget

By Depart - since we don't have budget for each month - we only need actual for now

               Month1          Month2        Month3

                actual$          actual$         actual$

Dep1

Dep2

Thanks a lot for your time

t1.png

Digvijay_Singh
Honored Contributor III

Re: Need help - one to many -two metrics - actual vs budget - two different sources

See, if this can help to get you started.

qvraj123
Contributor II

Re: Need help - one to many -two metrics - actual vs budget - two different sources

Hi Digvijay

Thank you so much for your time; The only issue is with my dataset as I have some records which does not have corresponding budget amount and vice versa - but this is really helpful

Rj

Digvijay_Singh
Honored Contributor III

Re: Need help - one to many -two metrics - actual vs budget - two different sources

You are welcome! Thanks.

qvraj123
Contributor II

Re: Need help - one to many -two metrics - actual vs budget - two different sources

Hi Digvijay,

I have another dataset from a different file - This is very high level

I have DEPT, NODE and OUTLOOK AMOUNT - what is the best way to get this in the model

Table 1 and Table 2 are joined with DEPT concatenated with CHARGE# but in Table 3 I do not have CHARGE#

I only have DEPT and NODE

I need to get outlook amount in the following - I have a bar graph for each DEPT and need to get Outlook Amt

ss1.png

Thanks for your time

Digvijay_Singh
Honored Contributor III

Re: Need help - one to many -two metrics - actual vs budget - two different sources

Can you Pl see if taking attached as base works for you.179125.PNG

Community Browser