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

[t_chetirbok]

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.

[Colin-Albert]

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.

[#4]

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.

[#5]

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.

[t_chetirbok]

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)

[Gysbert_Wassenaar]

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

[#8]

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.

[#9]

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

[t_chetirbok]

Hello!

Maybe something like this