Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I need to redesign the below logic.
The match can't be in the set anlysis as it relates to whether the Item Tax is + or -, but I want the item tax to only be for Current data type.
=if(Match([Entry Type Desc],'Creditor Credit Note' ,'Debtor Invoice', 'Creditor Payment','Debtor Refund'),
Avg(
{<
[Property Data Type]={'Current'}
>}
-[Item Tax])
,Avg(
{<
[Property Data Type]={'Current'}
>}[Item Tax]))
I tried this but not showing anything.
avg( {< [Entry Type Desc] ={"Creditor Credit Note*" ,"Debtor Invoice*", "Creditor Payment*","Debtor Refund*"} ,[Property Data Type]={'Current'}>}-[Item Tax])
+
avg( {< [Entry Type Desc]-={"Creditor Credit Note*" ,"Debtor Invoice*", "Creditor Payment*","Debtor Refund*"} ,[Property Data Type]={'Current'}>}[Item Tax])
show these vales as negative where entry type desc = "Creditor Credit Note*" ,"Debtor Invoice*", "Creditor Payment*","Debtor Refund*"
like
if(Match([Entry Type Desc],'Creditor Credit Note' ,'Debtor Invoice', 'Creditor Payment','Debtor Refund'),
sum(-[Item Tax]),sum([Item Tax]))
I don't want to use IF caluse.
Within the UI it will be always ugly and requiring a lot of efforts to develop and maintain such logic probably multiple times in a redundant manner and it will also impacting the performance.
I wouldn't tend to this way else doing the essential work within the data-model probably with a mapping-approach in which the value-direction will be directly adjusted and/or added as an extra field. With it simple aggregations like: sum(Field) or avg(Field) respectively with the extra flag something like: sum(Field * Flag) would be sufficient.
If-else statements impact the performence.
my suggestion, create one flag script level and pass it in set expression
if([Property Data Type]='Current' and Match([Entry Type Desc],'Creditor Credit Note' ,'Debtor Invoice', 'Creditor Payment','Debtor Refund'),1,0) as Matching_Flag
Expression:
Avg({<Matching_Flag={1}>}[Item Tax]))
Hi,
Added this in load script :: if(Match([Entry Type Desc],'Creditor Credit Note' ,'Debtor Invoice', 'Creditor Payment','Debtor Refund'),-1,1) as Entry_Category
in Chart :: avg({<[Property Data Type]={'Current'}>}[Item Tax])*Entry_Category
It works.
Thanks..