topic Sum each two rows in QlikView

Sum each two rows

Sun, 11 Dec 2011 19:23:00 GMT

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

Sum each two rows

swuehl — Sun, 11 Dec 2011 20:56:59 GMT

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

Re: Sum each two rows

Mon, 12 Dec 2011 17:37:00 GMT

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

Sum each two rows

swuehl — Mon, 12 Dec 2011 18:51:58 GMT

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

Re: Sum each two rows

Tue, 13 Dec 2011 11:59:36 GMT

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

Thank you very much!