Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all, hope some of you can help me.
As you can see the % is right for each row but not for the TOTAL. I guess that's because I'm mixing different items to the aggr (ITEM_LINE vs. INVOICE). Any suggestion?
This is a sample of my table and my code:
IF(Match(ITEM, 'A'),
(Sum(Aggr(SUM({$<ItemYear={$(=vCurrentYear)}, ITEM = {"A"}>} BASE_FAC)
/ Sum({$<ItemYear={$(=vCurrentYear)}, ITEM = {"A"}>} HOURS), ITEM_LINE)) -
Sum(Aggr(SUM({$<ItemYear={'$(=vCurrentYear-1)'}, ITEM = {"A"}>} BASE_FAC)
/ Sum({$<ItemYear={'$(=vCurrentYear-1)'}, ITEM = {"A"}>} HOURS), ITEM_LINE))) /
Sum(Aggr(SUM({$<ItemYear={'$(=vCurrentYear-1)'}, ITEM = {"A"}>} BASE_FAC)
/ Sum({$<ItemYear={'$(=vCurrentYear-1)'}, ITEM = {"A"}>} HOURS), ITEM_LINE)),
(Sum(Aggr(SUM({$<ItemYear={$(=vCurrentYear)}, ITEM -= {"A"}>} BASE_FAC)
/ Count({$<ItemYear={$(=vCurrentYear)}, ITEM -= {"A"}>} distinct INVOICE), ITEM_LINE)) -
Sum(Aggr(SUM({$<ItemYear={'$(=vCurrentYear-1)'}, ITEM -= {"A"}>} BASE_FAC)
/ Count({$<ItemYear={'$(=vCurrentYear-1)'}, ITEM -= {"A"}>} distinct INVOICE), ITEM_LINE))) /
Sum(Aggr(SUM({$<ItemYear={'$(=vCurrentYear-1)'}, ITEM -= {"A"}>} BASE_FAC)
/ Count({$<ItemYear={'$(=vCurrentYear-1)'}, ITEM -= {"A"}>} distinct INVOICE), ITEM_LINE)))
Many TKS!
Many TKS Vineeth,
That does not work for me because I'm working with straight table not pivot.
Anyway I did find out an easer way:
(Column(2) - Column(4)) / Column(4)
Rgds.
=if(Dimensionality() = 0
,
Expression to show in TOTAL row goes here
,
IF(Match(ITEM, 'A'),
(Sum(Aggr(SUM({$<ItemYear={$(=vCurrentYear)}, ITEM = {"A"}>} BASE_FAC)
/ Sum({$<ItemYear={$(=vCurrentYear)}, ITEM = {"A"}>} HOURS), ITEM_LINE)) -
Sum(Aggr(SUM({$<ItemYear={'$(=vCurrentYear-1)'}, ITEM = {"A"}>} BASE_FAC)
/ Sum({$<ItemYear={'$(=vCurrentYear-1)'}, ITEM = {"A"}>} HOURS), ITEM_LINE))) /
Sum(Aggr(SUM({$<ItemYear={'$(=vCurrentYear-1)'}, ITEM = {"A"}>} BASE_FAC)
/ Sum({$<ItemYear={'$(=vCurrentYear-1)'}, ITEM = {"A"}>} HOURS), ITEM_LINE)),
(Sum(Aggr(SUM({$<ItemYear={$(=vCurrentYear)}, ITEM -= {"A"}>} BASE_FAC)
/ Count({$<ItemYear={$(=vCurrentYear)}, ITEM -= {"A"}>} distinct INVOICE), ITEM_LINE)) -
Sum(Aggr(SUM({$<ItemYear={'$(=vCurrentYear-1)'}, ITEM -= {"A"}>} BASE_FAC)
/ Count({$<ItemYear={'$(=vCurrentYear-1)'}, ITEM -= {"A"}>} distinct INVOICE), ITEM_LINE))) /
Sum(Aggr(SUM({$<ItemYear={'$(=vCurrentYear-1)'}, ITEM -= {"A"}>} BASE_FAC)
/ Count({$<ItemYear={'$(=vCurrentYear-1)'}, ITEM -= {"A"}>} distinct INVOICE), ITEM_LINE))) )
Many TKS Vineeth,
That does not work for me because I'm working with straight table not pivot.
Anyway I did find out an easer way:
(Column(2) - Column(4)) / Column(4)
Rgds.