Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis: field equal to another field

sum(if([PCG Code]=[PCG],(Credit-Debit)))

Hi everyone.

The above code is working perfectly, but I want to put this into set analysis.

I have tried

sum({$<[PCG Code] = [PCG]>}(Credit-Debit))

but it keeps giving me zero.

Could anyone please assist me?

Thanks

3 Replies
Not applicable
Author

Try:

sum({$<[PCG Code] = P([PCG])>}[Credit-Debit])

It will depend on what PCG is and where it comes from. If PCG is different for each dimension, then it won't really work in Set Analysis. Set Analysis can't really be evaluated line by line based on dimension.

Not applicable
Author

Thanks for the feedback. I am not 100% sure this is what I am looking for.

Could you tell me what the "P" stands for or means?

I will test this formula and let you know.

Thanks

Not applicable
Author

This is what I am trying to do:

A Journal has the following fields:

1) Debit and Credit

2) Account Code

3) PCG Code (Cost/Profit Centre Code)

I were able to build a proper Income statement on Qlikview based on an Excel mapping file.

This file added certain Account Codes to certain lines with blank lines etc.

Then there came a new request:

I need to break down lets say Overheads into Overheads by PCG horizontally (not using a Pivot Table).

This means that certain Cost/Profit Centres get summarised as a line and other cost/profit centres get summarised as another line.

I have now updated my Excel mapping file which shows per line what account codes should be summarised aswell as which cost/profit centres.

That is why I posted the above question regarding this formula

sum({$<[PCG Code] = [PCG]>}(Credit-Debit))

I want qlikview to only sum debit-credit where the PCG Code on the journals are equal to the PCG I placed on the excel sheet. If there are not PCG on excel I need to sum the total debit-credit.

Does this make more sense?

See attached for a practical example.

Thanks