Summing two fields in script

[Anonymous]

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!

[Anonymous]

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

[bhelms]

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;

[maxgro]

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

     .......

[Anonymous]

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!

[bhelms]

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

[Anonymous]

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

Thanks

[bhelms]

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;

[Anonymous]

ok thanks!

[NZFei]

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;