Sum each two rows

[#1]

Hi, i need to sum the value of the second fortnight of each month with the value of the first fortnight of each previous month.

The Festive is as expression calculated by: count({distinct <Turn-={"V*"},Turn-={"B*"},Festive={"Yes"}>}  Turn)

I don't know if its also possible to avoid the blank cells to give as result something like:

Fortnights	Sums
2012-01-1	6
2012-01-2 + 2012-02-1	7
2012-02-2 + 2012-03-1	6
....	....

I tried  without success this:

count({distinct <Turn-={"V*"},Turn-={"B*"},Festive={"Yes"}>}  Turn)+above(count({distinct <Turn-={"V*"},Turn-={"B*"},Festive={"Yes"}>}  Turn))

Hope you can help me

clearer

[swuehl]

May I ask why you get blank cells at all, I would expect to see e.g. 11 for 2012-01-2 as sum of this Festive and above Festive using your given expression, here I assume you have one dimension, Fortnight.

Could you explain how you retrieve / calculate your fortnight? I think it would be easiest to create a new field for the periods you are interested in, periods than spanning the periods of two fortnights and named accordingly.

Regards,

Stefan

[#3]

Sorry, the complete expression i am using is:

if(right(Fortnight,1)='1',count({distinct <Turn-={"V*"},Turn-={"B*"},Festive={"Yes"}>}  Turn)+above(count({distinct <Turn-={"V*"},Turn-={"B*"},Festive={"Yes"}>}  Turn)))

what i try to do is count the Turns from the day 16 of one month until the 15 day of the next month and so with the rest of months.

Before use the expression, in script i calculate a new dimension with this:

if(day(date(date#(vDay&'-'&vMonth&'-'&vYear,'DD-MM-YYYY'),'DD-MM-YYYY'))<16,vYear& '-' & vMonth& '-' & '1',vYear& '-' & vMonth& '-' & '2') as Fortnight

Regards

[swuehl · Accepted solution]

Since this definition of your date period for two fortnights is static, I would suggest creating an additional field in the script, like:

DATE:

LOAD *,

if(right(Fortnight,1)=1, year(addmonths(Date,-1)) & '-' & month(addmonths(Date,-1))& '-' & '2' &' + ' & Fortnight,

    Fortnight & ' + ' & year(addmonths(Date,1)) & '-' & month(addmonths(Date,1))& '-' & '1')

            as TwoFortnights,

if(right(Fortnight,1)=1, if(month(Date)>1,year(addmonths(Date,-1)) & '-' & month(addmonths(Date,-1))& '-' & '2' &' + ' & Fortnight, Fortnight),

    if(month(Date)<12,Fortnight & ' + ' & year(addmonths(Date,1)) & '-' & month(addmonths(Date,1))& '-' & '1',Fortnight))

            as TwoFortnights2;

LOAD month(Date) as Month,Date,

if(day(Date)<16,year(Date)& '-' & month(Date)& '-' & '1',year(Date)& '-' & month(Date)& '-' & '2') as Fortnight

;

LOAD

Date(makedate(2011,01,01)+recno()-1) as Date

AutoGenerate 365;

Then you can just use TwoFortnights as dimension in your chart.

Hope this helps,

Stefan

[#5]

Excellent!!! I just need to transform the months to numeric format

Thank you very much!