Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Sarath123
Contributor III
Contributor III

How to write a script to define sales for each quarter

I have the data as attached below.

Lastime I calculated the sales based on the code by taking the sum of sales for the particular code

Now I am trying to caculate the sales for each quarter by using my caculated codes to find them
for code 3101 the sales should be sum of sales(3101)-sales(31010140)-sales(31010160)-sales(31010107)

for code 3201 the sales should be sum of sales (3201)-sales(32010140)-sales(32010160)-sales(32010107)

suppose for quarter Q2 2019 sales for both 3101&3201 should be exchanged with the caculated sales by using the above condition. I don't need the sum of sales instead for each quarter I need the sales value.
Like this I need to change my data for all the quarters (9quarters)with the new calculated values so that I can sum the sales values later
Can someone help me to define that on the script
My final output should be as
code      TU_company_QTR          TU_Total_QTR                     quarter                year 
3101              xxxxxx                                xxxxxx                                    Q2                       2019
3101              xxxxxx                                xxxxxx                                    Q1                        2019
.
.

3101              xxxxxx                              xxxxxxx                                   Q2                          2017
where   xxxx will be the new calculated sales by using  below  condition 
3101 should be sum of sales(3101)-sales(31010140)-sales(31010160)-sales(31010107)


Can someone help me to define that on the script

1 Solution

Accepted Solutions
Brett_Bleess
Former Employee
Former Employee

Sarath, about the best I can do here is point you to the Design Blog area of Community, there are hundreds of posts here on how to do different things.  I am not exactly sure what you are trying to accomplish here, so I am going to give you the top level link, and there is a search box there you can use to try to find the posts that seem like they may help.  Best advice on search terms would be to start with a single word and see what you get back and then try to narrow from those results.  I believe you should be able to find at least a few posts that should be along the lines of what you want to do here that should help you possibly sort it out on your own at that point.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.

View solution in original post

3 Replies
Sarath123
Contributor III
Contributor III
Author

Can someone help me on my query please

Brett_Bleess
Former Employee
Former Employee

Sarath, about the best I can do here is point you to the Design Blog area of Community, there are hundreds of posts here on how to do different things.  I am not exactly sure what you are trying to accomplish here, so I am going to give you the top level link, and there is a search box there you can use to try to find the posts that seem like they may help.  Best advice on search terms would be to start with a single word and see what you get back and then try to narrow from those results.  I believe you should be able to find at least a few posts that should be along the lines of what you want to do here that should help you possibly sort it out on your own at that point.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Sarath123
Contributor III
Contributor III
Author

Austria:
LOAD * INLINE [
code, company, market
15500140, 0, 0
15500140, 0, 0
15500140, 0, 0
15500140, 0, 0
15500140, 0, 0
15500140, 0, 0
15500140, 0, 0
15500140, 0, 0
15500140, 0, 0
15500240, 0, 0
15500240, 0, 0
15500240, 0, 0
15500240, 0, 0
15500240, 0, 0
15500240, 0, 0
15500240, 0, 0
15500240, 0, 0
15500240, 0, 0
15904001, 9000, 0
15904001, 0, 0
15904001, 0, 0
15904001, 0, 0
15904001, 0, 0
15904001, 0, 0
15904001, 0, 0
15904001, 0, 0
15904001, 0, 0
15904002, 0, 0
15904002, 7000, 0
15904002, 11000, 0
15904002, 8000, 0
15904002, 10000, 0
15904002, 7000, 0
15904002, 4000, 0
15904002, 0, 0
15904002, 0, 0
15904003, 1000, 0
15904003, 0, 0
15904003, 0, 0
15904003, 0, 0
15904003, 0, 0
15904003, 0, 0
15904003, 0, 0
15904003, 0, 0
15904003, 0, 0
1547, 30000, 2027000
1547, 20000, 3070000
1547, 18000, 2103000
1547, 18000, 2323000
1547, 13000, 1938000
1547, 19000, 2309000
1547, 10000, 1661000
1547, 0, 1715000
1547, 0, 1593000
];

LOAD *,
RowNo() as Row
Resident Austria;

New:
Load *,
If(Rowno()=46,company-(Peek(company,-9)+Peek(company,-18)+Peek(company,-27)+Peek(company,-36)+Peek(company,-45)),company-(Peek(company,-9)+Peek(company,-18)+Peek(company,-27)+Peek(company,-36)+Peek(company,-45)))as company_New,

If(Rowno()=46,market(Peek(market,-9)+Peek(market,-18)+Peek(market,-27)+Peek(market,-36)+Peek(market,-45)),market-(Peek(market,-9)+Peek(market,-18)+Peek(market,-27)+Peek(market,-36)+Peek(market,-45)))as total
Resident Austria;
DROP Table Austria;


I have loaded the data using Inline and used Peek function to define the sales.But when I am trying to write for whole data which has 17000 rows with different codes I am getting an error.
Can someone help me to derive for whole data, and If possible an idea to use which function 
peek,crosstable,Rowno() is also appreciated
waiting for your response!!