New to QlikView

Discussion board where members can get started with QlikView.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for
Did you mean:
Highlighted
Not applicable

Variable / Set analysis ( I think)

Hi Community,

I have a problem with a straight table and not sure if I am going in the right direction.

I would like a straight table that shows the main costs and the additional costs for that company ignoring any other additional costs from other companies

I have 2 tables one with the main purchase and another table with additional purchases

looks a bit like this

 Flight ID Company No. Net Cost 123456 AMERICA 5000

 Flight ID Buy From No Add Costs Sent to Accounts 123456 FLL 1,000.28 Yes 123456 AMERICA 5,065.12 Yes 123456 AMERICA 465.3 Yes 123456 REP/GODFREY 830 Yes

What I would like to see in a straight table is

 Flight ID Company No. Net Costs Additional Cost 123456 AMERICA 5000 6,065.40

This just shows the costs and additional costs of the company.

But I get

 Flight ID Company No. Net Costs Additional Cost 123456 AMERICA 5000 7,360.70

Which is the total of the all the other companies and not just the main company.

For this one I did this

Then I tried adding some set analysis / variable (I don't realty understand them all that well, so I am completely doing it wrong)

(SUM({\$<[Operator No.]={\$(=only(Operator No.))},[Sent to Accounts]={'1'}>}[Additional Cost]))

I just get 0 as a result.

Mark

1 Solution

Accepted Solutions
Highlighted
Creator III

I guess you would need to sum up only if the Sent to Accounts is Yes.

if so the expression would be:

10 Replies
Highlighted
Creator III

You have the same ID for all the companies. So when you try to get a straight table including Id, it sums up all the values in Additional costs.

How did you get 6,065.40 here? Which additional costs are you summing up?

Highlighted
Not applicable

Hello Reshma,

Thank you for your response. That cost is made up of the two "AMERICA" records in the Buy From No

 123456 AMERICA 5,065.12 Yes 123456 AMERICA 465.3 Yes

I want to ignore the two lines

 123456 FLL 1,000.28 Yes

and

 123456 REP/GODFREY 830 Yes

My aim is to do a cost analysis on our suppliers as some times the costs are hidden in taxes and extras. let say there is 1 'enquiry' and it costs 1,000. This 1,000 may be made up of 400, 300, 200 and 100. Only the 400 and 100 are from our main supplier the other 200 and 300 are from sub suppliers. I would like to ignore the 200 and 300 value and see the 400 as the main cost and 100 as the extra.

I hope that makes more sense.

Thanks again!

Mark

Highlighted
Not applicable

Sorry I seem to have got my figures incorrect. the total should have been 5530.42 for the additional cost. The two records from 'AMERICA'

Highlighted
Partner

set analysis can also exclude values if you use -= :

Sum({<[Company No]-={"FLL","REP/GODFREY"} >} [Net Costs])

Highlighted
Creator III

Please see the attached solution. Thanks

Highlighted
Not applicable

Hi that seemed to work, The last part is to only sum by 'Sent to accounts'. As some times the line is outstanding or cancelled.

a bit like this

SUM(IF({\$<[Operator No.]=[Buy From No],[Sent to Accounts]={'1'}>}[Net Amount]))

I know this works for totalling up all the lines

Thanks!

Mark

Highlighted
Creator III

I guess you would need to sum up only if the Sent to Accounts is Yes.

if so the expression would be:

Highlighted
Not applicable

Hi Reshma,

That seems to do the trick. Thank you for your time, you have been a great help.

Thanks

Mark

Highlighted
Not applicable

Thank you for this information it is useful