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.

[upaliwije]

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

[israrkhan]

Thanks for your time..

[MK_QSL]

Dear Mr. Khan,

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

[israrkhan]

Dear Manish,

kindly find the Attachment,

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

[mambi]

Hi,

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

[israrkhan]

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

[mambi]

Hope this will be helpful

[israrkhan]

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

[IAMDV]

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,

DV