16 Replies Latest reply: Apr 5, 2016 9:24 AM by Filiberto Cacciari

# Percentage of the total with two dimensions crossing

Hi everyone,

what I'm looking for is to display the percentage of the total with two dimensions crossing.

What I would like is to have the % of Pay of the total of Pay per Data crossed per Buyer.

I tried but does not work...

Then what I would like to do is to compare the value of month-year (mar-15) with the month-year before (mar-14) and display it in mar-15 and don't display anymore mar-14. This for every month-year of 15. No display month-year of 14.

The example down there.

Is that clear enough?

Here attached my qvw.

Thank you all.

Filiberto

• ###### Re: Percentage of the total with two dimensions crossing

May be this:

Sum(Pay)/Sum(TOTAL <Date>Pay)

• ###### Re: Percentage of the total with two dimensions crossing

Something like this for your second requirement?

• ###### Re: Percentage of the total with two dimensions crossing

Hi guys sunindia and swuehl, what if I want to display in the MonthYear-n, the sum of MonthYear-n+1 ; MonthYear-n+2 ; MonthYear-n+23.

What I should do?

I tried with this one but doesn't work....

I think that my problem is that I have to consider 2 dimensions.

I don't if I'm clear enough with my explanation.

Here my new qvw.

Thanks.

Filiberto !

What do you think gwassenaar?

• ###### Re: Percentage of the total with two dimensions crossing

I am not sure I understand completely. Would it be possible to export this chart into Excel and replace the expected number using Excel formulas?

• ###### Re: Percentage of the total with two dimensions crossing

Yes. Attached here.

As u can see CY is the sum of the next 3 months and PY is the sum of the next 3 months of the PY.

Can you better understand now?

thx so much

stalwar1

• ###### Re: Percentage of the total with two dimensions crossing

Some scripts modifications were required, but it seems to work now

Script:

TABLE_TEMP:

01/03/2014,  Cand, 10, 1,  Open

01/04/2014,  Ben, 21, 2,  Closed

01/05/2014,  Cand, 12, 4,  Open

01/06/2014,  Ben, 22, 5,  Closed

01/03/2015,  Ben, 10, 7,  Open

01/04/2015,  Ben, 21, 3,  Closed

01/05/2015,  Al, 12, 1,  Open

01/06/2015,  Al, 22, 2,  Closed

01/03/2014,  Ben, 8, 4,  Open

01/04/2014,  Cand, 9, 3,  Closed

01/05/2014,  Ben, 10, 4,  Open

01/06/2014,  Cand, 21, 6,  Closed

01/03/2015,  Cand, 12, 3,  Open

01/04/2015,  Cand, 22, 3,  Closed

01/05/2015,  Cand, 23, 2,  Open

01/06/2015,  Ben, 24, 3,  Open

01/03/2014,  Al, 25, 1,  Open

01/04/2014,  Al, 10, 2,  Open

01/05/2014,  Al, 10, 4,  Open

01/06/2014,  Al, 21, 3,  Open

01/03/2015,  Al, 12, 5,  Open

01/04/2015,  Al, 13, 2,  Open

01/05/2015,  Ben, 14, 2,  Open

01/06/2015,  Cand, 15, 3,  Open

01/03/2014,  Cand, 10, 1,  Open

01/04/2014,  Ben, 11, 2,  Open

01/05/2014,  Cand, 12, 4,  Open

01/06/2014,  Ben, 13, 1,  Open

01/03/2015,  Ben, 14, 2,  Open

01/04/2015,  Ben, 15, 4,  Open

01/05/2015,  Al, 16, 1,  Open

01/06/2015,  Al, 17, 2,  Open

01/03/2014,  Ben, 18, 4,  Open

01/04/2014,  Cand, 19, 1,  Open

01/05/2014,  Ben, 20, 2,  Closed

01/06/2014,  Cand, 21, 4,  Closed

01/03/2015,  Cand, 22, 1,  Closed

01/04/2015,  Cand, 23, 2,  Closed

01/05/2015,  Cand, 24, 4,  Closed

01/06/2015,  Ben, 25, 1,  Closed

01/03/2014,  Al, 26, 2,  Closed

01/04/2014,  Al, 27, 4,  Closed

01/05/2014,  Al, 28, 1,  Closed

01/06/2014,  Al, 29, 2,  Closed

01/03/2015,  Al, 30, 4,  Closed

01/04/2015,  Al, 31, 1,  Closed

01/05/2015,  Ben, 32, 2,  Closed

01/06/2015,  Cand, 33, 4,  Closed

];

TABLE:

Pay,

Day,

CAT,

Date,

AutoNumber(Num(Month(Date))) - 1 as MonthNum

Resident TABLE_TEMP

Order By Date desc;

DROP Table TABLE_TEMP;

AsOf:

Date,

'CY' as FlagYear

Resident TABLE;

Concatenate (AsOf)

'PY' as FlagYear

Resident TABLE;

Expressions

1) Previous Year: RangeSum(After(Sum({<FlagYear = {'PY'}>}Pay), 1, MonthNum))

2) Current Year: RangeSum(After(Sum({<FlagYear = {'CY'}>}Pay), 1, MonthNum))

• ###### Re: Percentage of the total with two dimensions crossing

That's helps, but is not exactly what I was looking for.

In this case if I add a MonthYear in the loading data, like a new month 01/07/2015, I see in the CY 01/03/2105 value the sum of the next 4 months that exists. I want the sum of the next 3 months and not of all the next existing months.

Enough clear?

Here attached my new qvw.

stalwar1

Thank u so much.

Filiberto

• ###### Re: Percentage of the total with two dimensions crossing

So you want a fixed period range?

RangeSum(After(Sum({<FlagYear = {'PY'}>}Pay), 1, 3))  // was: , MonthNum))

• ###### Re: Percentage of the total with two dimensions crossing

Yes I want a fixed period of three months.

As you can see in the attached file down here, your solution works only for the 2015 but not for the 2014.

Any idea?

Thanks u both.

• ###### Re: Percentage of the total with two dimensions crossing

That's because you show only some months, but across several years.

How do you want to handle year bounderies with regard to 'show next three months'?

I assume, it would be sufficient to create data for all months, without having gaps inbetween.

• ###### Re: Percentage of the total with two dimensions crossing

Stefan is right, you need to provide some more data for other years and lay out what exactly you would want to see. The more information you provide, the easier it will be to provide a working solution

• ###### Re: Percentage of the total with two dimensions crossing

Attached a modified version with an AsOf table based on month, not only CY / PY.

Using this, you'll the correct pay values accumulated even when your table is showing only part of the year or when making selections in your dimension (it doesn't use chart inter records anymore).

In short, an AsOf-table approach.

The As-Of Table

• ###### Re: Percentage of the total with two dimensions crossing

Guys you're the best! thak u so much for your great help! swuehl stalwar1

• ###### Re: Percentage of the total with two dimensions crossing

For your first request, try TOTAL qualifier with field list:

sum(Pay)/  sum(total<Date>(Pay))

• ###### Re: Percentage of the total with two dimensions crossing

For your second request, look into the concept of an AsOf table:

The As-Of Table

• ###### Re: Percentage of the total with two dimensions crossing

Does this look right?

Script: (Change in Red)

TABLE_TEMP:

01/03/2014,  Cand, 10, 1,  Open

01/04/2014,  Ben, 21, 2,  Closed

01/05/2014,  Cand, 12, 4,  Open

01/06/2014,  Ben, 22, 5,  Closed

01/03/2015,  Ben, 10, 7,  Open

01/04/2015,  Ben, 21, 3,  Closed

01/05/2015,  Al, 12, 1,  Open

01/06/2015,  Al, 22, 2,  Closed

01/03/2014,  Ben, 8, 4,  Open

01/04/2014,  Cand, 9, 3,  Closed

01/05/2014,  Ben, 10, 4,  Open

01/06/2014,  Cand, 21, 6,  Closed

01/03/2015,  Cand, 12, 3,  Open

01/04/2015,  Cand, 22, 3,  Closed

01/05/2015,  Cand, 23, 2,  Open

01/07/2014,  Cand, 20, 2,  Open

01/07/2015,  Cand, 23, 2,  Open

01/06/2015,  Ben, 24, 3,  Open

01/03/2014,  Al, 25, 1,  Open

01/04/2014,  Al, 10, 2,  Open

01/05/2014,  Al, 10, 4,  Open

01/06/2014,  Al, 21, 3,  Open

01/03/2015,  Al, 12, 5,  Open

01/04/2015,  Al, 13, 2,  Open

01/05/2015,  Ben, 14, 2,  Open

01/06/2015,  Cand, 15, 3,  Open

01/03/2014,  Cand, 10, 1,  Open

01/04/2014,  Ben, 11, 2,  Open

01/05/2014,  Cand, 12, 4,  Open

01/06/2014,  Ben, 13, 1,  Open

01/03/2015,  Ben, 14, 2,  Open

01/04/2015,  Ben, 15, 4,  Open

01/05/2015,  Al, 16, 1,  Open

01/06/2015,  Al, 17, 2,  Open

01/03/2014,  Ben, 18, 4,  Open

01/04/2014,  Cand, 19, 1,  Open

01/05/2014,  Ben, 20, 2,  Closed

01/06/2014,  Cand, 21, 4,  Closed

01/03/2015,  Cand, 22, 1,  Closed

01/04/2015,  Cand, 23, 2,  Closed

01/05/2015,  Cand, 24, 4,  Closed

01/06/2015,  Ben, 25, 1,  Closed

01/03/2014,  Al, 26, 2,  Closed

01/04/2014,  Al, 27, 4,  Closed

01/05/2014,  Al, 28, 1,  Closed

01/06/2014,  Al, 29, 2,  Closed

01/03/2015,  Al, 30, 4,  Closed

01/04/2015,  Al, 31, 1,  Closed

01/05/2015,  Ben, 32, 2,  Closed

01/06/2015,  Cand, 33, 4,  Closed

];

TABLE:

Pay,

Day,

CAT,

Date,

RangeMin(AutoNumber(Num(Month(Date))) - 1, 3) as MonthNum

Resident TABLE_TEMP

Order By Date desc;

DROP Table TABLE_TEMP;

AsOf: