topic Re: How to write a script to define sales for each quarter in QlikView

How to write a script to define sales for each quarter

Sarath123 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to write a script to define sales for each quarter

Sarath123 — Tue, 24 Sep 2019 12:56:45 GMT

Can someone help me on my query please

Re: How to write a script to define sales for each quarter

Brett_Bleess — Wed, 02 Oct 2019 15:04:13 GMT

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

Re: How to write a script to define sales for each quarter

Sarath123 — Tue, 08 Oct 2019 14:38:37 GMT

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!!