# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Action-Packed Learning Awaits! QlikWorld 2023. April 17 - 20 in Las Vegas: REGISTER NOW
cancel
Showing results for
Did you mean:
Creator II

## 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

1 Solution

Accepted Solutions
MVP

May be this:

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

16 Replies
MVP

May be this:

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

MVP

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

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

MVP

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

The As-Of Table

MVP

Something like this for your second requirement?

Creator II
Author

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‌?

MVP

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?

Creator II
Author

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

MVP

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))

Creator II
Author

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.

Thank u so much.

Filiberto

Community Browser