Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Excellent!!! I just need to transform the months to numeric format
Thank you very much!