Calculated dimension

[LaureDenivelle]

Hello

I'm using Qlik Sense Enterprise May 2023 Patch 6 (we will be soon on Qlik Sense SaaS if it can make a difference).

Here are my diffculties to answer the business need. I can't do the calculation in the script because the business can chose any period for the calculation. 

This is the table ...

that I need to transform into this :

Here are the measures :

- ANew Debit : SUM({< Type = {'ANEW'} >} Debit_Amount)

- ANew Credit : SUM({< Type = {'ANEW'} >} Credit_Amount)

- Debit Period (Not ANew) : SUM({< Type -= {'ANEW'} >} Debit_Amount)

- Credit Period (Not ANew) : SUM({< Type -= {'ANEW'} >} Credit_Amount)

- Debit balance : IF( SUM(Debit_Amount) - SUM(Credit_Amount) > 0, SUM(Debit_Amount) - SUM(Credit_Amount),0 )

- Credit balance : IF( SUM(Debit_Amount) - SUM(Credit_Amount) <= 0, SUM(Credit_Amount) - SUM(Debit_Amount),0 )

Here are the rules : 

- Only ACCOUNT starting by 401* or 408* must be calculated into a new ACCOUNT depending on the debit or credit balance. When balance = 0 then it's Credit

- The agregation is on the ENTITY and the ACCOUNT

- For example, AEG001-AEGSA | 40100000-SI2P become AEG001-AEGSA | 40100000-Credit supplier because the balance = 0

- The other ACCOUNT stay the same, no change

- Users can chose any period and this is the diffucultie. I can't pre-calculate the state of a supplier (to know if it is credit or debit)

Here is an application with a set of data.

I hope this is clear enough.

Thanks for your help.

Regards 

Laure

[brunobertels · Accepted solution]

Hi Laure 

Not sure to understand well the logic , trying to reproduce the desired output with a calculated dimension , i used this formula 

=aggr(IF( sum(aggr(SUM(Debit_Amount) + SUM(Credit_Amount),ENTITY,ACCOUNT)) >= 0 and ACCOUNT like '4010*','401000000-Credit Supplier',

IF( sum(aggr(SUM(Debit_Amount) + SUM(Credit_Amount),ENTITY,ACCOUNT)) > 0 and ACCOUNT like '4010*','401000000-Debit Supplier',

ACCOUNT)),ACCOUNT)

with it i have this but not the expected result : 

first line seems ok but others not but i don't understand why , netherthe less i hope it could help 

Regards 

[LaureDenivelle]

Hi @brunobertels 

Thank you for your answer. 

Your solution is working in part. 

In fact, in the final application, if I let the detail dimension (with account + detail account name) it's working but if I remove it, it's not working.

But I will quote your answer as a solution because it's working and I ask the business to export the chart and make a quick pivot table in Excel as a solution. They are OK with it. 

[brunobertels]

Hi Laure 

glad it helped