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: 
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

(SUM({$<[Sent to Accounts]={'1'}>}[Additional Cost]))

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.

I hope this is clear enough, please get in touch if you need any more information

Thank you for your time

Mark

1 Solution

Accepted Solutions
reshmakala
Creator III
Creator III

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

if so the expression would be:

sum({<SentToAccounts={Yes}>}if(CompanyNo=BuyFromNo,AddCosts))

View solution in original post

10 Replies
reshmakala
Creator III
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?

Not applicable
Author

Hello Reshma,

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

123456AMERICA5,065.12Yes
123456AMERICA465.3Yes

I want to ignore the two lines

123456FLL1,000.28Yes

and

123456REP/GODFREY830Yes

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

Not applicable
Author

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'

giakoum
Partner - Master II
Partner - Master II

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

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

reshmakala
Creator III
Creator III

Please see the attached solution. Thanks

Not applicable
Author

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

(SUM({$<[Sent to Accounts]={'1'}>}[Additional Cost]))

Thanks!

Mark

reshmakala
Creator III
Creator III

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

if so the expression would be:

sum({<SentToAccounts={Yes}>}if(CompanyNo=BuyFromNo,AddCosts))

Not applicable
Author

Hi Reshma,

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

Thanks

Mark

Not applicable
Author

Thank you for this information it is useful