Pivot Table Rows.

[israrkhan]

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.

[israrkhan]

Hi, Deepak

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

Kind Regards

Khan

[IAMDV]

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

Cheers,

DV

[#4]

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

[israrkhan]

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

[#6]

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

[#7]

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

[jonathandienst]

Hi

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