A few questions...Load Script, Sum similar transactions.

[#1]

Hi guys, i am really really new to Qlikview and have encountered some problems which i hope you guys can help me with. Been searching around for answer and trying but doesn't really work.

So an example of my data are as follow:

Transaction No	Customer ID	Product Code	TransYear	TransMonth	Price
00001	1	AA1	2014	1	100
00002	2	AA2	2014	2	150
00003	1	AA5	2014	2	300
00004	2	AA1	2014	2	600
00005	2	AA2	2014	3	200
00006	1	AA1	2014	3	300
00007	1	AA2	2014	3	88

These are just first few of my data, there are thousands of transactions.

Questions:

1) When i load my script, i tried, date(makedate(TransYear,TransMonth),'MM-YYYY') as TransDate,

   However, the product that appeared as Transdate does not match TransYear and Transmonth of that particular Customer/Transaction.

   For Example, if i open a straight table with all the following data above, the TransDate would be, say for transaction 00001, 05-2015 and

   not 01-2014 as i hoped for. But when i input that formula as expression, it works perfectly fine.

2) I want to get sum purchased by each customer and also by product code. So in expression i would put

sum({<Customer ID,TransMonth>}Price) under the column, SpentMonth, and this would work fine. But after that, i also would like to perform another operation, by summing the SpentMonth for only month 1 and 2, which i cannot do since SpentMonth is not actually a field.

So as i have searched for answers around this forum, a method of solving this would be to perform the SpentMonth in the load script.

But i have absolutely no idea how since set analysis doesnt work in load script part.

It would really be awesome, if someone could kindly shine some light on this.

I sincerely thank you for taking the time to read and reply to this question.

Thank you

[effinty2112]

Hi Lance,

I looks like a pivot table would give you what you need:

Customer ID	Product Code	TransYear	TransMonth	Sum(Price)
1	AA1	2014	1	100
			3	300
	AA2	2014	3	88
	AA5	2014	2	300
2	AA1	2014	2	600
	AA2	2014	2	150
			3	200

You can add subtotals (partial sums) to any or all of the dimensions:

Customer ID	Product Code	TransYear	TransMonth	Sum(Price)
1	AA1	2014	1	100
			3	300
			Total	400
		Total		400
	AA2	2014	3	88
			Total	88
		Total		88
	AA5	2014	2	300
			Total	300
		Total		300
	Total			788
2	AA1	2014	2	600
			Total	600
		Total		600
	AA2	2014	2	150
			3	200
			Total	350
		Total		350
	Total			950
Total				1738

[#3]

Hi there, like you mentioned i can add subtotal right, but what if i dont want to sum all of them up?

For example, the table you attached,

Customer ID	Product Code	TransYear	TransMonth	Sum(Price)
1	AA1	2014	1	100
			3	300
			4	200
	AA2	2014	3	88
	AA5	2014	2	300
2	AA1	2014	2	600
	AA2	2014	2	150
			3	200

I added another row to month 4 as you cans see the one without borders.

What if now i just want to sum month 3 and 4 together?

And then later i want it to be divided by 2 to show a ratio?

Thanks

[#4]

Hi there, i cant open the file, mine is personal edition, it is saying i need to change my user key or smth.

[#5]

Transaction No	Product Code	TransYear	TransMonth	Price
00001	AA1	2014	1	100
00002	AA2	2014	2	150
00003	AA1	2014	3	300
00004	AA1	2014	4	600
00005	AA2	2014	5	200
00006	AA1	2014	7	300
00007	AA2	2014	8	200
00008	AA2	2015	1	88
00009	AA2	2015	2	88
000010	AA2	2015	3	88
000011	AA2	2015	5	88
000012	AA2	2015	7	88
000013	AA2	2015	8	88
000014	AA2	2015	9	88

Okay here is as precise as it gets to what i want to do.

My end goal is a ratio.

So now we are at the end of 2015.

i want a Table that says:

Date             ProductCode     Ratio

Nov-2015        AA1                sum({<TransYear='2015',4<=TransMonth<=8>}Price)/sum({<TransYear='2014',4<=TransMonth<=8>}Price)

then will be

OCT-2015   AA1 ................................ and so on

Before i can do this sum, i already have sum of sales broken down by product code and by Month and year as shown

in this pivot table

Customer ID	Product Code	TransYear	TransMonth	Sum(Price)
1	AA1	2014	1	100
			3	300
			4	200
	AA2	2014	3	88
	AA5	2014	2	300
2	AA1	2014	2	600
	AA2	2014	2	150
			3	200

*This is not the complete one* its just to say i have it broken down by year month and product code.

[#6]

Hi, my problem with real data set

Tricky question (With data and qlikview sheet attached)

Hope you can help

Thank you!

[#7]

Tricky question (With data and qlikview sheet attached)

My problem with real data set. (Excel output and qlikview summary)

Hope you guys can help.

Thank you