Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have an expression in a pivot table, its a simple IF() statement,
if(column(1) >0 and Column(2)=0,1,0).
The expression is giving me what i want but i am trying to get a total at the top which i am struggling to do,
I have tried using SUM(AGGR()) around it over the dimensions but it doesnt work.
Is anyone able to take a look please?
I attached a QVW.
Many thanks in advance.
Hi,
try to use this:
If(Dimensionality()=0,Sum({<[Revision Reason]=>}Aggr(if(SUM({<[Revision Reason]=>}[Quantity Invoiced - Cases])>0 and SUM ([Quantity Canceled - Cases])=0,1,0), [Order Number])),
(if([CA Invoiced]>0 and [CA Cancelled]=0,1,0)))
Try this for the expression in EXP1:
if(Dimensionality()=0,
count(distinct [Order Number]),
if(([CA Invoiced]>0 and [CA Cancelled]=0),count(distinct [Order Number]),0)
)
Thanks and regards,
Arthur Fong
Hi, thanks for your reply.
That solution gives me a total but its the wrong total as a lot of the rows are now incorrect.
i.e.
The highlighted rows should be 1 as there is a value in 'CA Invoiced' and CA Cancelled is 0.
The total i am looking for should be 2743.
Hi,
try to use this:
If(Dimensionality()=0,Sum({<[Revision Reason]=>}Aggr(if(SUM({<[Revision Reason]=>}[Quantity Invoiced - Cases])>0 and SUM ([Quantity Canceled - Cases])=0,1,0), [Order Number])),
(if([CA Invoiced]>0 and [CA Cancelled]=0,1,0)))
Thats great, thanks very much!
maybe this:
=sum( aggr( if( SUM({<[Revision Reason]=>}[Quantity Invoiced - Cases])>0 and SUM ([Quantity Canceled - Cases])=0, 1, 0 ), [Order Number] ) )