
To find the sum of a field for last day of month
jagan mohan rao appala Feb 16, 2012 6:35 AM (in response to samyukthams)Hi,
You can easily do this by adding a additional column in your script
LOAD
Date,
If(Date = MonthEnd(Date), 1, 0) AS IsMonthEnd,
'
'
'
FROM DataSource;
Now by the month end has 1 in IsMonthEnd Field.
In expression now you get the values as
=Sum({<IsMonthEnd={1}>} SalesCount)
Hope this helps you.
Regards,
Jagan.

To find the sum of a field for last day of month
samyukthams Feb 16, 2012 7:17 AM (in response to jagan mohan rao appala )Hi.
MonthEnd(Date) gives me last day of the month say for Feb 2012 it gives 29Feb2012, but say i have data only till 10th of Feb then i need the sum of salescount for 10th of Feb.
How to achieve this?

To find the sum of a field for last day of month
Marc Livingston Feb 16, 2012 7:30 AM (in response to samyukthams)Using month as a dimension try this:
sum(if(Date=max(Date),SalesCount))

Re: To find the sum of a field for last day of month
Stefan Wühl Feb 16, 2012 7:31 AM (in response to Marc Livingston)Try advanced aggregation:
=sum(aggr(if(Date=max(total<Pdt,Month> Date),SalesCount),Pdt,Month,Date))

comm47424.qvw 137.2 K

To find the sum of a field for last day of month
jagan mohan rao appala Feb 16, 2012 8:05 AM (in response to Stefan Wühl )Hi,
The expression suggested by Swuehl works perfectly for you scenario. I think the Date should be removed at the end of the expression. The modified expression would be
=sum(aggr(if(Date=max(total<Pdt,Month> Date),SalesCount),Pdt,Month))
Regards,
Jagan.

To find the sum of a field for last day of month
Quentin Raith Feb 29, 2012 4:56 AM (in response to jagan mohan rao appala )Thank you guys, it helped me a lot to calculate stock level, instead of using firstsortedvalue().
I have one more question: is it possible to integrate set analysis using this expression?
I am using a Master Calendar and would like for i.e. to sum the last 12 months to make average
Thanks,
Quentin




