Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

caccio88
New Contributor III

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?

gwassenaarswuehlmaxgro

Here attached my qvw.

Thank you all.

Filiberto

1 Solution

Accepted Solutions

Re: Percentage of the total with two dimensions crossing

May be this:

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

Capture.PNG

16 Replies

Re: Percentage of the total with two dimensions crossing

May be this:

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

Capture.PNG

MVP
MVP

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

MVP
MVP

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

Something like this for your second requirement?

Capture.PNG

caccio88
New Contributor III

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

Sum(aggr(rangesum(below(total sum({<Flag ={'CY'} >} Pay),1,3)),AsOfDate,Buyer))

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?

caccio88
New Contributor III

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

sunindia

Re: Percentage of the total with two dimensions crossing

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

Capture.PNG

Script:

TABLE_TEMP:

LOAD * INLINE [

    Date,  Buyer,  Pay,  Day,  CAT

    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:

LOAD Buyer,

  Pay,

  Day,

  CAT,

  Date,

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

Resident TABLE_TEMP

Order By Date desc;

DROP Table TABLE_TEMP;

AsOf:

LOAD Date as AsOfDate,

  Date,

  'CY' as FlagYear

Resident TABLE;

Concatenate (AsOf)

LOAD Date as AsOfDate,

  Date#( AddYears(Date, -1),'DD/MM/YYYY') as Date,

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

caccio88
New Contributor III

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.

sunindia

Thank u so much.

Filiberto

Community Browser