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: 
mahitham
Creator II
Creator II

If logic help

Hi Experts,
Can any one please help me on below requirement.
I have an Average Net Amount expression like below here I am dividing the Sum of Net Amount by Number of Repairs.

Sum(Aggr(
(Sum({<[Date]={">=$(=addmonths(today(),-12))"},[Type]={'Level1'},[ Name]={'AA','BB','CC','DD'}>}[Net Amount])

/
Count({<[Date]={">=$(=addmonths(today(),-12))"},[Type]={'Level1'},[ Name]={'AA','BB','CC','DD'}>}distinct[Repairs])

),[MonthYear]))


Here requirement is
when [Status] = Completed then don't add any VAT on [Net Amount] need to consider the actual amount like(Sum({<[Date]={">=$(=addmonths(today(),-12))"},[Type]={'Level1'},[ Name]={'AA','BB','CC','DD'}>}[Net Amount]) but
when [Status] = Not Completed then need to increase 30% on Net Amount value
Please help me to write the logic.

Thanks in advance.

1 Solution

Accepted Solutions
rahulpawarb
Specialist III
Specialist III

May be this:

Sum(Aggr(
(Sum({<[Date]={">=$(=addmonths(today(),-12))"},[Type]={'Level1'},[ Name]={'AA','BB','CC','DD'}>} If([Status] = 'Not Completed', 1.3, 1) * [Net Amount])

/
Count({<[Date]={">=$(=addmonths(today(),-12))"},[Type]={'Level1'},[ Name]={'AA','BB','CC','DD'}>}distinct[Repairs])

),[MonthYear]))

View solution in original post

3 Replies
rahulpawarb
Specialist III
Specialist III

May be this:

Sum(Aggr(
(Sum({<[Date]={">=$(=addmonths(today(),-12))"},[Type]={'Level1'},[ Name]={'AA','BB','CC','DD'}>} If([Status] = 'Not Completed', 1.3, 1) * [Net Amount])

/
Count({<[Date]={">=$(=addmonths(today(),-12))"},[Type]={'Level1'},[ Name]={'AA','BB','CC','DD'}>}distinct[Repairs])

),[MonthYear]))

mahitham
Creator II
Creator II
Author

Hi @rahulpawarb 

Could you please explain why 1.3 is considered. I assume for 30% need to multiply with 0.03.

rahulpawarb
Specialist III
Specialist III

For example, Net Amount is 1,000 and Status is Not Completed then amount should be 1,300:
//Solution 1
=1,000 + (1,000*0.3)
//Solution 2
=1,000 * 1.3

Both the expression will return same result.

Hope this will help.