topic Re: Percentage of the total with two dimensions crossing in QlikView

Percentage of the total with two dimensions crossing

caccio88 — Wed, 23 Mar 2016 22:18:11 GMT

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?

gwassenaar‌ swuehl‌ maxgro‌

Here attached my qvw.

Thank you all.

Filiberto

Re: Percentage of the total with two dimensions crossing

sunny_talwar — Wed, 23 Mar 2016 22:22:06 GMT

May be this:

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

Re: Percentage of the total with two dimensions crossing

swuehl — Wed, 23 Mar 2016 22:23:53 GMT

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

swuehl — Wed, 23 Mar 2016 22:26:20 GMT

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

sunny_talwar — Wed, 23 Mar 2016 22:31:54 GMT

Something like this for your second requirement?

Re: Percentage of the total with two dimensions crossing

caccio88 — Thu, 31 Mar 2016 23:06:03 GMT

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

sunny_talwar — Fri, 01 Apr 2016 01:14:49 GMT

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

caccio88 — Fri, 01 Apr 2016 06:09:52 GMT

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

sunny_talwar — Fri, 01 Apr 2016 09:31:44 GMT

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

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

Re: Percentage of the total with two dimensions crossing

caccio88 — Fri, 01 Apr 2016 14:21:35 GMT

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

Re: Percentage of the total with two dimensions crossing

swuehl — Fri, 01 Apr 2016 14:35:39 GMT

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

caccio88 — Fri, 01 Apr 2016 15:12:15 GMT

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.

sunindia‌ swuehl‌

Re: Percentage of the total with two dimensions crossing

swuehl — Fri, 01 Apr 2016 15:21:19 GMT

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

sunny_talwar — Fri, 01 Apr 2016 16:30:44 GMT

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

swuehl — Fri, 01 Apr 2016 16:53:33 GMT

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

sunny_talwar — Fri, 01 Apr 2016 23:20:20 GMT

Does this look right?

Script: (Change in Red)

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/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:

LOAD Buyer,

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:

LOAD Date as AsOfDate,

Date,

'CY' as FlagYear

Resident TABLE;

Re: Percentage of the total with two dimensions crossing

caccio88 — Tue, 05 Apr 2016 13:24:56 GMT

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