topic Pivot Table Rows. in QlikView

Pivot Table Rows.

israrkhan — Sat, 15 Feb 2014 08:42:44 GMT

Hi Community,

i have a very simple a tricky calculation in a pivot table, please share your idea... i am stuck here.

i have pivot table with two dimensions and 1 expression, Dimensions are Year and Country, and expression is sum(sale).

as you can see in image, for Abu Dhabi my first sale in Year "1996", and for Bahrain my first sale is in year "1995".

i have another field in same table called "Rate", and Rate having values like 0.99, 0.95, 0.75, 0.60..... 0.00. (20 values fixed).

now i have to multiply my first year sale(113) by first value of rates(0.99), in year 1996,

and in second row year = 1997 , 113 will be multiple by second "Rate" which is 0.95. and will be added in to 190.

and same value 113 will be multiply by 0.75, in third row of year 1998. and in fourth row (year = 1999), 113 will be multiple by 0.60, and it will go on up to 20 rows.

Your comments would be highly appreciated.

Re: Pivot Table Rows.

upaliwije — Sat, 15 Feb 2014 09:03:22 GMT

I am unable to comment on this since I too am not very conversant in set expression

Re: Pivot Table Rows.

israrkhan — Sat, 15 Feb 2014 09:05:27 GMT

Thanks for your time..

Re: Pivot Table Rows.

MK_QSL — Sat, 15 Feb 2014 09:45:30 GMT

Dear Mr. Khan,

would like to try. can you share your sample excel file or apps?

Re: Re: Pivot Table Rows.

israrkhan — Sat, 15 Feb 2014 10:22:37 GMT

Dear Manish,

kindly find the Attachment,

have you understood , what i am expecting...? if not kindly ask?

Re: Pivot Table Rows.

mambi — Sat, 15 Feb 2014 10:39:18 GMT

Hi,

do you want to multiply only your first sales (113) by the 20 rates or all your sales by the 20 rates ?

Re: Pivot Table Rows.

israrkhan — Sat, 15 Feb 2014 11:26:00 GMT

Thank for Time Badi....

i think you got my idea...

yes all sale, for 1996 sale 113 will be multiply by 20 rate up to 20 year up 2016.

and second row 1997 sale 190 will be multiple by 20 rates up to 2017.

but 113 will be multiply by first rate value(1.00) in 1996, and in next year 1997 sale value 113 will be multiply by second rate value (0.99), and in next year 1998 sale value 113 will be multiply by third Rate value (0.97)...

and same Sale value of 1997 (190) will be multiply by 1.00 in 1997, and in next year 1998 190 will be multiply by second rate value (0.99) up to 20 Rate 2017.

and third year 1998 sale value (305) will be multiply by 1.00 in 1998, and in next year 1999, 190 will be multiple by second value of Rate 0.99, and in 2000, 190 will be multiply by third value of Rate(0.97).. up to 2018..

is it clear...?

Re: Re: Pivot Table Rows.

mambi — Sat, 15 Feb 2014 15:16:37 GMT

Hope this will be helpful

Re: Pivot Table Rows.

israrkhan — Sat, 15 Feb 2014 17:27:16 GMT

Dear Badi,

thank you very much for your Effort, and time.

your did multiplication, with only first value of sale (113) for year 1996. with all 20 rates.

but i have to do the same for all rows,(all years)

for the second year same multiplication will start, and for the third also same. and will be added to next year.

its type of recursive call.

so finally i think its not possible in qlikview..will try in excel..

Thanks Again.....

Kind Regards

Khan

Re: Re: Pivot Table Rows.

IAMDV — Sat, 15 Feb 2014 17:44:26 GMT

Hi Khan,

Please can you provide the end result in excel file? I'd love to take up this challenge. I don't want to see this message...

so finally i think its not possible in qlikview..will try in excel..

Thanks,

Re: Re: Pivot Table Rows.

israrkhan — Sun, 16 Feb 2014 10:17:44 GMT

Hi, Deepak

i will prepare and upload detail document, with excel example(expected result). today evening.

Kind Regards

Khan

Re: Re: Pivot Table Rows.

IAMDV — Sun, 16 Feb 2014 11:51:13 GMT

Thank you Khan. I shall look forward for the challenge!

Cheers,

Re: Pivot Table Rows.

Sun, 16 Feb 2014 13:36:24 GMT

Hi Israr,

I saw your message a little late but it seems I can give you an answer. Not sure I have understood completely the problem but you will tell me.

2 solutions.

Either in the UI : rangesum(above(sum(Sales*Rate), 0, RowNo() )

The problem with that solution: it computes starting the first row in the chart

Or in the script:

Sales:
NoConcatenate

LOAD *,
if (CountryID = previous(CountryID), Rangesum(Sales*Rate, peek(CumulSales)), Sales*Rate) as CumulSales

Resident TempSales

ORDER BY CountryID, Year;

See the xls and qvw attached.

Fabrice

Re: Pivot Table Rows.

israrkhan — Sun, 16 Feb 2014 17:16:15 GMT

Hi Fabric And Deepak,

Thank you for your time...

Fabric i think you did not understand what i am looking for.

Deepak, here are the details...

Case:(Requirement).

1) A Car Seller Company Have Yearly Sale For different Countries.

and they keep record of their sold cars upto 20 years (they make Parts for cars upto 20 years).

let say they sold 100 cars in 1980, and will make parts for those cars till 2000, and in 2001 they assume that cars are not on the road any more.and they will not make parts for these 100 cars in 2000, same as the cars they sold in 1981, they will assume that the cars are not on the road in 2001, after 20 years. so they are making parts on this analysis.

How They Calculate....

They Have "Rates" values, 1 to 20,(20 fixed values) will start from first sale year, and go upto twenty.

Rates are Here... 1.00 , 0.99 , 0.97 , 0.95 , 0.90, 0.82 , 0.68, 0.5, 0.31, 0.20, 0.14, 0.11, 0.08, 0.06, 0.05, 0.04, 0.03, 0.02,0.01, 0.00...

Now let say we have US sale Like Below...

Year ... Sale ..... Expected Result

.....................................................................................

1994 --- 0 ------ Ignore Zero, Start from first sale > 0

1995 --- 0 -----

1996 --- 113 ---- 113 * 1.00 = Result?

1997--- 190 ----- 190 * 1.00 + 113 * 0.99 = Result ?

1998--- 230 ---- 230 * 1.00 + 190 * 0.99 + 113 * 0.97

1999--- 243 ----- 243 * 1.00 + 230 * 0.99 + 190 * 0.97 + 113 * 0.95

2000--- 256 ----- 256 * 1.00 + 243 * 0.99 + 230 * 0.97 + 190 * 0.95 + 113 * 0.90

2001--- 273 ----

2002--- 302 -----

2003---- 305 -----

2004--- 333 ----

2005---- 405 -----

2006---- 419 -----

2007---- 735 -----

2008----

2009----

2010----

2011----

2012---

2013------

2014-----

2015---

2016----

.......................

Hope its Clear....

If Not Kindly Ask me....

Kind Regards

Khan

Re: Re: Pivot Table Rows.

Sun, 16 Feb 2014 22:25:21 GMT

Israr,

Effectively, I did not understand your first request.

Please find therefore my second answer. To get what you want, you need to multiply the lines (20 lines per year of sales) and sum them. it is why I use iterno() and the left join to do the multiplication. Afterwards, the sum is automatic in QlikView but I do the sum to get the computation ready to use.

The script is documented. See the XLS file used as an example.

There is perhaps a simpler way to do: if you find (or if someone finds a simpler way), please share it (I still want to learn.

Cheers

Fabrice

Re: Pivot Table Rows.

Mon, 17 Feb 2014 06:30:21 GMT

Hi Israr,

You can try the below as well by adding this in your loading script

for i = 0 to NoOfRows('SalesVolumes')-1

let v_Year = peek('Year', i, 'SalesVolumes');

let v_Volume = peek('Volumes', i, 'SalesVolumes');

let v_Dealer = peek('Dealer', i, 'SalesVolumes');

ProjectedVolumes:

LOAD $(v_Year) as Year, ($(v_Volume) * 1.0) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

LOAD ($(v_Year)+1) as Year, ($(v_Volume) * 0.99) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

LOAD ($(v_Year)+2) as Year, ($(v_Volume) * 0.975) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

LOAD ($(v_Year)+3) as Year, ($(v_Volume) * 0.95) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

LOAD ($(v_Year)+4) as Year, ($(v_Volume) * 0.9) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

LOAD ($(v_Year)+5) as Year, ($(v_Volume) * 0.82) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

LOAD ($(v_Year)+6) as Year, ($(v_Volume) * 0.68) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

LOAD ($(v_Year)+7) as Year, ($(v_Volume) * 0.5) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

LOAD ($(v_Year)+8) as Year, ($(v_Volume) * 0.31) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

LOAD ($(v_Year)+9) as Year, ($(v_Volume) * 0.2) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

LOAD ($(v_Year)+10) as Year, ($(v_Volume) * 0.14) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

LOAD ($(v_Year)+11) as Year, ($(v_Volume) * 0.1) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

LOAD ($(v_Year)+12) as Year, ($(v_Volume) * 0.08) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

LOAD ($(v_Year)+13) as Year, ($(v_Volume) * 0.065) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

LOAD ($(v_Year)+14) as Year, ($(v_Volume) * 0.05) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

LOAD ($(v_Year)+15) as Year, ($(v_Volume) * 0.04) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

LOAD ($(v_Year)+16) as Year, ($(v_Volume) * 0.03) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

LOAD ($(v_Year)+17) as Year, ($(v_Volume) * 0.02) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

LOAD ($(v_Year)+18) as Year, ($(v_Volume) * 0.01) as [Projected Volumes], '$(v_Dealer)' as Dealer AutoGenerate(1) Where $(v_Volume) > 0 and (not(IsNull($(v_Volume))));

next i;

So you will get the desired result as below

Hope this is what you wanted to achieve.

Regards,

Sajeevan

Re: Re: Pivot Table Rows.

jonathandienst — Mon, 17 Feb 2014 07:38:42 GMT

Your could do something like:

Data:
LOAD * Inline
[
Country,Year,Sales
United Kingdon,2000,171
United Kingdon,2001,139
..
Australia,2010,131
Australia,2011,172
Australia,2012,55
...
South Africa,1990,29
South Africa,1991,58
South Africa,1992,136
...
South Africa,2029,29
South Africa,2030,76
];

Factors:
LOAD RowNo() As Index, Factor Inline
[
  Factor
  1.00
  0.99
  0.97
  0.95
  0.90
  0.82
  0.68
  0.5
  0.31
  0.20
  0.14
  0.11
  0.08
  0.06
  0.05
  0.04
  0.03
  0.02
  0.01
  0.00
];

// Get the start year for each country
Join (Data)
LOAD Country,
  Min(Year) As StartYear
Resident Data
Group By Country;

// Get the year index (offset from start year)
Join (Data)
LOAD Country,
  Year,
  Year - StartYear + 1 As YearIndex
Resident Data;

// Add the factor value based on the year index
Join (Data)
LOAD Index As YearIndex,
  Factor As cFactor
Resident Factors;

// Calculate the cumulative factored sales
Join (Data)
LOAD Country,
  Year,
  If (Country = Previous(Country),
  Sales * Alt(cFactor, 0) + Peek(Cumulative),
  Sales
  ) As Cumulative
Resident Data
Order By Country, Year;

HTH

Jonathan