Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

dftoomey
New Contributor II

Margin for Selected Category

Hello,

I am attempting to create a table that will produce the margin % for a specific category. I have been attempting to use the following code:

(Sum({<[Distribution Channel] = {'Parts'}>}if([Total Revenue]<100 and [Total Revenue]>1,[Total Revenue],''))
-
Sum({<[Distribution Channel] = {'Parts'}>}if([Total Revenue]<100 and [Total Revenue]>1,[Total Cost],'')))
/
(
Sum({<[Distribution Channel] = {'Parts'}>}if([Total Revenue]<100 and [Total Revenue]>1,[Total Revenue],'')))

Currently, this only outputs 100%. I believe that the cost portion is coming in as 0 because the if statement is dependent on the "Total Revenue" instead of the "Total Cost". Any thoughts on how to remedy the issue? Is there a way to pull the category in the code before I perform the sum function?

Thanks,

Dave

6 Replies
atkinsow
Valued Contributor II

Re: Margin for Selected Category

Try


if([Total Revenue]<100 and [Total Revenue]>1,


(Sum({<[Distribution Channel] = {'Parts'}>},[Total Revenue])
-
Sum({<[Distribution Channel] = {'Parts'}>}[Total Cost]))
/
(
Sum({<[Distribution Channel] = {'Parts'}>}[Total Revenue])),'')

dftoomey
New Contributor II

Re: Margin for Selected Category

Thanks for the response. That version of the code pulls no data (displays "No data to display" in my chart). Thoughts?

atkinsow
Valued Contributor II

Re: Margin for Selected Category

Hard to say without seeing your data.  Maybe add an aggregation?

if(sum([Total Revenue])<100 and sum([Total Revenue])>1,


(Sum({<[Distribution Channel] = {'Parts'}>},[Total Revenue])
-
Sum({<[Distribution Channel] = {'Parts'}>}[Total Cost]))
/
(
Sum({<[Distribution Channel] = {'Parts'}>}[Total Revenue])),'')

dftoomey
New Contributor II

Re: Margin for Selected Category

I tried this...

num(if(Sum({<[Distribution Channel] = {'Parts'}>}[Total Revenue])>1 and Sum({<[Distribution Channel] = {'Parts'}>}[Total Revenue])<100,

(
Sum({<[Distribution Channel] = {'Parts'}>}[Total Revenue])
-
Sum({<[Distribution Channel] = {'Parts'}>}[Total Cost]))
/
Sum({<[Distribution Channel] = {'Parts'}>}[Total Revenue]),''),'#,##0%')

However, it just pulls one month that does not have the total cost and revenue completed (month hasn't closed yet). Thoughts?

atkinsow
Valued Contributor II

Re: Margin for Selected Category

Can you share a sample app?

dftoomey
New Contributor II

Re: Margin for Selected Category

What is that. Also, how would I go about it?

Community Browser