topic Re: A few questions...Load Script, Sum similar transactions. in QlikView

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

Wed, 25 Nov 2015 16:37:55 GMT

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

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

t_chetirbok — Wed, 25 Nov 2015 17:55:33 GMT

Hello!

1) You should change DateFormat: SET DateFormat='MM-YYYY';

and try this date(TransMonth&'-'&TransYear) as Year_Month

2) Can you show some example what do you want to get finally (for your data in question). We'll answer more exactly for you question.

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

Colin-Albert — Wed, 25 Nov 2015 18:08:46 GMT

Hi Lance,

Dates are the cause of many questions on the forum.

Try using makedate(TransYear,TransMonth) as TransDate

This will create a datefield of the first of the month, as you have not supplied a day value to makedate.

These blogs should help you with dates.

https://community.qlik.com/blogs/qlikviewdesignblog/2012/06/07/get-the-dates-right

https://community.qlik.com/docs/DOC-3102

Don't try to answer specific queries through the load script. The load script should contain data for every month

For example don't calculate sales for month 1 and month 2 in the load script.

To get the sales for months 1 and 2 use a chart and either let the users select which months they want from a listbox, or use set analysis in the chart expression.

If you can post a few rows of your source data this would help the community point you in the right direction.

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

Thu, 26 Nov 2015 01:18:19 GMT

Hi Tatsiana and Colin,

Thank you for your replies.

As Colin mentioned, the loadscript should contain data for every month, hence i am trying to

get a field that does sum({<Customer ID,TransMonth>}Price) using the load script.

How do i write that in loadscript?

As mentioned i would like to perform a sum on top of another expression, but i cannot do that since the expression is not a field. Are there any ways around this?

As for an example where i want to get to in the end,

I want to know total spent for every quarter of the year by customer, and product code.

So i would like to know for each customer, how much do they spend each quarter.

Similarly i would like to know for each specific product, how much was sold out.

That is the reason why i am trying to do a sum on top of another sum.

Thank you.

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

Thu, 26 Nov 2015 01:46:29 GMT

First column is TransYear, 2nd is TransMth, third is the makedate. It screws up if i use it in loadscript.

But if i manually add a calculated dimension using makedate, it works fine.

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

t_chetirbok — Thu, 26 Nov 2015 06:31:04 GMT

Hi!

I share some example. You can add Quarter field: ceil(TransMonth/3) . And your chart dimensions are Product / Customer and Quarter, expression - sum(Price)

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

Gysbert_Wassenaar — Thu, 26 Nov 2015 08:08:22 GMT

Can you post a small qlikview document with some sample data to illustrate the problem?

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

Thu, 26 Nov 2015 08:44:20 GMT

The sample data is really on top.

What i want to achieve is,

i want 2 tables in the end

Table 1:

Currently we are in year, so i want to display 2014 value as 2015.

TransYear	TransMonth	TransDate	Price	Current Date	Calculation
2014	1	01-2014	100	01-2015	100
2014	2	02-2014	150	02-2015	150+300+600
2014	2	02-2014	300	03-2015	200+300+88
2014	2	02-2014	600
2014	3	03-2014	200
2014	3	03-2014	300
2014	3	03-2014	88

*THE CURRENT DATE AND CALCULATION* is what i want to achieve. Do i need to do a separate straight table for that? Maybe that is why it is not working all these while.

Table 2:

Customer ID	Product Code	TransYear	TransMonth	Price	Customer ID	Date	Total Spent By month	Total Spent in year
1	AA1	2014	1	100	1	01-2015 02-2015 03-2015	100 300 300+88	100+300+300+88
2	AA2	2014	2	150	2	01-2015 02-2015 03-2015	0 150+600 200	0+150+160+200
1	AA5	2014	2	300
2	AA1	2014	2	600
2	AA2	2014	3	200
1	AA1	2014	3	300
1	AA2	2014	3	88

For this table, The Customer ID,Date,Total Spent By Month and Total Spent in Year is what i want to achieve.

Thank you.

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

Thu, 26 Nov 2015 08:50:00 GMT

So basically i would like to

Date Sum(price)

2015 sum(price of 2014)

i tried to put into the field, sum({<AddYears(TransYr,-1)>}Price)

But the field doesnt display anything

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

t_chetirbok — Thu, 26 Nov 2015 10:16:24 GMT

Hello!

Maybe something like this

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

effinty2112 — Thu, 26 Nov 2015 13:51:17 GMT

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
	AA1	2014	3	300
	AA2	2014	3	88
	AA5	2014	2	300
2	AA1	2014	2	600
	AA2	2014	2	150
	AA2	2014	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
		2014	Total	88
		Total		88
	AA5	2014	2	300
		2014	Total	300
		Total		300
	Total			788
2	AA1	2014	2	600
		2014	Total	600
		Total		600
	AA2	2014	2	150
			3	200
			Total	350
		Total		350
	Total			950
Total				1738

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

Fri, 27 Nov 2015 01:38:42 GMT

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
	AA2	2014	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

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

Fri, 27 Nov 2015 01:40:39 GMT

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

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

Fri, 27 Nov 2015 02:01:23 GMT

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
	AA2	2014	3	200

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

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

Mon, 30 Nov 2015 00:47:06 GMT

Hi, my problem with real data set

Tricky question (With data and qlikview sheet attached)

Hope you can help

Thank you!

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

Mon, 30 Nov 2015 00:47:39 GMT

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