topic Re: Summing two fields in script in QlikView

Summing two fields in script

Anonymous — Tue, 15 Jul 2014 19:59:54 GMT

Hi, I understand that the sum used together with group by. How'd I apply it in the script though? Let's say to do these:

1. sum both OEM and POS sales as total Sales.

2. Then, I need to calculate total sales for new products released within a certain window.

For example:

Load

OEM_sale,

POS_sale,

Year_Qtr,

Year_Qtr_Product_Released,

Sum(if((Year_Qtr - Year_Qtr_Product_Released) <= 30 and (Year_Qtr - Year_Qtr_Product_Released) >= 0, Total Sales)

Can anyone help, please?

Thanks in advance!

Re: Summing two fields in script

Anonymous — Tue, 15 Jul 2014 20:13:17 GMT

i just had a group by issue and this thread helped me a lot...take a look:

Group By Error

for sum of OEM and POS, do you need them done in the script?

if yes, and may be i missed something in your requirement, but why not try something like below (in bold)

Load

OEM_sale,

POS_sale,

OEM_sale + POS_sale as Total_sale,

Year_Qtr,

Year_Qtr_Product_Released,

....

and to restrict the time frame, use this formula in if condition..something like:

if((Year_Qtr - Year_Qtr_Product_Released) <= 30 and (Year_Qtr - Year_Qtr_Product_Released) >= 0, OEM_sale+POS_sale, 0) as Total_sale

Re: Summing two fields in script

bhelms — Tue, 15 Jul 2014 20:15:59 GMT

If you are looking to aggregate in the script you could do something like the below script, but you could also do these aggregations within the application itself, without having to group by.

Load Year_Qtr,

Year_Qtr_Product_Released,

Sum(OEM_sale) as OEM_sale,

Sum(POS_sale) as POS_sale,

Sum(OEM_sale + POS_sale) as [Total Sales],

Sum(if((Year_Qtr - Year_Qtr_Product_Released) <= 30 and (Year_Qtr - Year_Qtr_Product_Released) >= 0, OEM_sale + POS_sale)) as [Total Period Sales]

Resident Table

Group By Year_Qtr, Year_Qtr_Product_Released;

Re: Summing two fields in script

maxgro — Tue, 15 Jul 2014 20:33:54 GMT

maybe you can just add a flag and manage it in the charts

expression for total sales = sum({$ <Flagnew....={1}>} OEM_sale + POS_sale)

Load

OEM_sale,

POS_sale,

Year_Qtr,

Year_Qtr_Product_Released,

if(( (Year_Qtr - Year_Qtr_Product_Released) <= 30 and

(Year_Qtr - Year_Qtr_Product_Released) >= 0,

1,0) as Flagnewproductsreleasedwithinacertainwindow

......

from

.......

Re: Summing two fields in script

Anonymous — Tue, 15 Jul 2014 20:43:35 GMT

Thank you guys, I did the flag initially, but I later realized that the flag would not work as well with other charts. Anyhow, I am going to try aggr method. Do I need to do the Resident table in order to get this to work?

Thanks again!

Re: Summing two fields in script

bhelms — Tue, 15 Jul 2014 20:45:55 GMT

No, that was just my example of your data. You didn't include the FROM statement so I made up my own.

Re: Summing two fields in script

Anonymous — Tue, 15 Jul 2014 20:56:19 GMT

Sorry.. 1 more question - the "Group by" statement goes after From ..Qvd, correct?

Thanks

Re: Summing two fields in script

bhelms — Tue, 15 Jul 2014 21:01:09 GMT

That is correct. If you are loading from a QVD, it would look something like this.

Load Year_Qtr,

Year_Qtr_Product_Released,

Sum(OEM_sale) as OEM_sale,

Sum(POS_sale) as POS_sale,

Sum(OEM_sale + POS_sale) as [Total Sales],

Sum(if((Year_Qtr - Year_Qtr_Product_Released) <= 30 and (Year_Qtr - Year_Qtr_Product_Released) >= 0, OEM_sale + POS_sale)) as [Total Period Sales]

From XYZ.qvd (qvd)

Group By Year_Qtr, Year_Qtr_Product_Released;

Re: Summing two fields in script

Anonymous — Tue, 15 Jul 2014 22:52:26 GMT

ok thanks!

Re: Summing two fields in script

NZFei — Wed, 16 Jul 2014 03:36:21 GMT

Load

Year_Qtr,

sum(OEM_sale)+sum(POS_sale) as TotalSale

from XXXXXX

where Year_Qtr - Year_Qtr_Product_Released<=30 and Year_Qtr - Year_Qtr_Product_Released>=0

group by Year_Qtr;

Re: Summing two fields in script

Anonymous — Wed, 16 Jul 2014 15:20:28 GMT

Hi thanks. I put the Sum(if(..) statement above the load as preceding load, pls see below. However, something about the scripts that it's not liking and it kinda just freezes on me when i debug it.

Load

Sum(if((Year_Qtr -Year_Qtr_Product_Released)<=30 and

(Year_Qtr -Year_Qtr_Product_Released)>=0, Ship_Value + POS_Revenue_Value)) as NP_3YR_Revenue,

Load

OEM_sale,

POS_sale,

Sum(OEM_sale) + sum(POS_sale) as Total_Sales,

applymap('Calendar_Map', Calendar_Id, 0) as Year_Qtr,

applymap('Product_Released_Map', Material_Id, 0) as Year_Qtr_Product_Released,

From

TotalSales.Qvd

(qvd)

Group by Year_Qtr, Year_Qtr_Product_Released;