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

Calculation in script based on a field but hold calculation against a new ID

Hi all, 

Wondering if someone can help me with this issue that I have. I have the following code:

 

NOCONCATENATE GLBALANCE_R:
LOAD [GL BAL KEY], 
     [BALANCE FILE SUMMARY LEVEL], 
     [GL COMPANY ID], 
     [PROFIT CENTRE ID], 
     [GL ACCOUNT MAJOR ID], 
     [PROFIT CENTRE ID] + [GL ACCOUNT MAJOR ID] as PFAM_L,
     [GL ACCOUNT MINOR ID], 
     [GL CURRENCY], 
     [YEAR END], 
     [OPENING BALANCE FOR YEAR], 
     [GL PERIOD NN], 
     [GL MOVEMENT BASE], 
     [GL PERIOD], 
     [CLOSING BALANCE BASE], 
     [GL MOVEMENT CONSOL1], 
     [GL MOVEMENT CONSOL2], 
     [CLOSING BALANCE CONSOL1], 
     [CLOSING BALANCE CONSOL2], 
     [GL ACCOUNT MINOR],
     [GL BAL KEY] & '%' & [GL PERIOD NN] as [GL_BAL_KEY_2]
Resident GLBalance;

DROP TABLE GLBalance;

 

What needs to happen is the following:

 

IF [GL COMPANY ID] = 01, 
Hold the variable [Opening Balance For Year] somewhere. 
IF [GL COMPANY ID] is 02,
Hold the variable [Opening Balance For Year] elsewhere.
Add these up together in a new field called [Opening Balance For Year Combined]

I also need to hold this data gainst a new [GL COMPANY ID] for example CO for Combined.

Repeat the same process above for [GL MOVEMENT BASE] and [CLOSING BALANCE BASE] and hold the new figures against the new [GL COMPANY ID] so that I can bring the data into a Pivot Chart.

 

The confusing part is that the resident table holds the values for both [GL COMPANY ID] 01 and 02. I want to keep all calculations within the script.

 

Thanks in advance for any help. I can attach the QVW application somehow if required.

1 Solution

Accepted Solutions
sunny_talwar

Is there a reason you don't use Sum([OPENING BALANCE FOR YEAR]) instead of [OPENING BALANCE FOR YEAR]... because if you use Sum([OPENING BALANCE FOR YEAR]), you can enable total to see this

image.png

View solution in original post

7 Replies
sunny_talwar

I think it might be easy to resolve this if you are able to share a qvw file.

Best,
Sunny

Ahmed_Turnaround
Author

Hi Sunny,


Thank you for your reply. Attached is an example. I can't send the whole application as there is sensitive data but this holds the data table in my previous reply and the inline table I have created for the 01/02/03. 

 

Thanks for any help. 

sunny_talwar

This is sample is good, but can you explain what is not as per your expectation? Is this something on the front end which doesn't look right?

image.png

Ahmed_Turnaround
Author

Hi Sunny,

 

Thank you for your reply. 

 

So, as you can see, the company ID's 01 and 02 have their respective opening, movement and closing. What needs to happen is have a combined figure and show that in it's own section in the pivot table. So there will be 03 or combined which will be the total opening/movement/closing. I'm not sure how to achieve this with an IF statement in the Expression. 

 

OR 

 

Have this calculation done in the script and hold it under the 03 company ID, so that I can then add it into the pivot table.

 

As you will see, if you go into the pivot chart properties and look at the expressions, I selected the Opening/Movement/Closing as an expression, so that it appears in the desired way in our pivot table. I basically want to have 3 separate showings of Opening/Movement/Closing, but the 3rd showing will be the totals of 01 and 02.

 

Ideally, I'd like the calculations done in the script as I believe this will be easier/quicker? 

 

Does this make sense with what I am trying to achieve?

 

Thank you

sunny_talwar

Is there a reason you don't use Sum([OPENING BALANCE FOR YEAR]) instead of [OPENING BALANCE FOR YEAR]... because if you use Sum([OPENING BALANCE FOR YEAR]), you can enable total to see this

image.png

Ahmed_Turnaround
Author

Hi Sunny,


Thank you for your reply. That's a good point, I didn't think of that. Let me try it in my main application with the sensitive data and I will get back to you. 

 

Bear with me. 

 

Thanks

Ahmed_Turnaround
Author

Hi Sunny,

 

Looks like your method worked. I have checked it and seems to be correct. Thank you very much for your assistance. I will raise another help case if required but great stuff.

 

Thanks,