topic Re: Variable / Set analysis ( I think) in QlikView

Variable / Set analysis ( I think)

Tue, 03 Feb 2015 13:32:11 GMT

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

Re: Variable / Set analysis ( I think)

reshmakala — Tue, 03 Feb 2015 15:07:40 GMT

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?

Re: Variable / Set analysis ( I think)

Tue, 03 Feb 2015 15:19:43 GMT

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

Re: Variable / Set analysis ( I think)

Tue, 03 Feb 2015 15:22:41 GMT

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'

Re: Variable / Set analysis ( I think)

giakoum — Tue, 03 Feb 2015 15:30:18 GMT

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

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

Re: Variable / Set analysis ( I think)

reshmakala — Tue, 03 Feb 2015 15:39:53 GMT

Please see the attached solution. Thanks

Re: Variable / Set analysis ( I think)

Tue, 03 Feb 2015 16:53:33 GMT

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

Re: Variable / Set analysis ( I think)

reshmakala — Tue, 03 Feb 2015 17:12:27 GMT

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

Re: Variable / Set analysis ( I think)

Tue, 03 Feb 2015 17:28:19 GMT

Hi Reshma,

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

Thanks

Mark

Re: Variable / Set analysis ( I think)

Tue, 03 Feb 2015 17:29:49 GMT

Thank you for this information it is useful

Re: Variable / Set analysis ( I think)

Anonymous — Thu, 05 Feb 2015 16:25:20 GMT

The following expression should also work using the Possible values function.

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

Regards,

Srikanth