Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
I have filter of Year & Month... I have requirement like when I click on Year, for the corresponding month I should get previous 3rd month amount without selecting month.
For eg. when I select year 2013 I have four months Jan, Feb , Mar , Apr.. so for Jan I should get previous 3rd month amount( i.e Amount for Oct-2012)
Also I want to do it all the calculation on front end only as data model is freezed.
I dont have MonthYear or Date field created from backend, otherwise it was simple to implement.
I am using following setanalysis expression & dimenstion is
Dimension = Daily_Sales_Fin_Month_Name
Expression :
=sum({$<Daily_Sales_Fin_Month_Name={'$(=upper(date(AddMonths(date(date#(Daily_Sales_Fin_Month_Name&Daily_Sales_Comm_Fin_Year,'MMMYY'),'MMM-YY'),-3),'MMM')))'}>}BookSize_Debtor_AR_POS)
But I am not able to get the data using this expression. I treid a lot but failed to get the data.
Can anyone please help on this?
Please find the attached application with testdata.
May b this will be helpful
Try this...
=
sum({$<Daily_Sales_Fin_Month_Name={'$(=upper(Date(MonthEnd(AddMonths(Max(Date(date#(Daily_Sales_Fin_Month_Name,'MMMYY')),-3)),'MMM') ))'}>}BookSize_Debtor_AR_POS)
Hi
thanks for your quick reply but this expression is not working for me..
have you tried with the qvw which i have aatched ?
Hi Kush,
I would suggest to create a new field called MonthNum. The reason we can easy point in time
LOAD *,
Num(Month(Date#(Daily_Sales_Fin_Month_Name,'MMM'))) AS [MonthNum];
LOAD * Inline [
Daily_Sales_Comm_Fin_Year,Daily_Sales_Fin_Month_Name,BookSize_Debtor_AR_POS
2012,APR,100
2012,MAY,50
2012,JUN,150
2012,JUL,175
2012,AUG,200
2012,SEP,210
2012,OCT,220
2012,NOV,130
2012,DEC,90
2013,JAN,100
2013,FEB,180
2013,MAR,300 ];
Now create Straight Table with
1. Dimension: Daily_Sales_Fin_Month_Name
2. Expression: Sum({$<MonthNum = {$(=Max(MonthNum)-3)}>} BookSize_Debtor_AR_POS)
See sample attached file.
Regards,
Sokkorn
Hi,
Thanks for your reply but your solution is not working for me.
Please see the output i need .
Please see the attched screenshot for the output i need
May b this Help you ....
you need to hard code as you said you can edit so i have changed your Month Jan, Feb, ... to 1 ,2,3,4 ...
I have created a Variable :
vMaxYearMaxMonth = =$(=max({<Daily_Sales_Comm_Fin_Year={$(=max(Daily_Sales_Comm_Fin_Year))}>} Daily_Sales_Fin_Month_Name))
this will give you current Month as data is dummy
simply need add the expression like
=if(vMaxYearMaxMonth = 1,
sum({<Daily_Sales_Comm_Fin_Year = {$(=max(Daily_Sales_Comm_Fin_Year)-1)},
Daily_Sales_Fin_Month_Name = {10}>} BookSize_Debtor_AR_POS),
if(vMaxYearMaxMonth = 2,
sum({<Daily_Sales_Comm_Fin_Year = {$(=max(Daily_Sales_Comm_Fin_Year)-1)},
Daily_Sales_Fin_Month_Name = {11}>} BookSize_Debtor_AR_POS),
if(vMaxYearMaxMonth = 3,
sum({<Daily_Sales_Comm_Fin_Year = {$(=max(Daily_Sales_Comm_Fin_Year)-1)},
Daily_Sales_Fin_Month_Name = {12}>} BookSize_Debtor_AR_POS),
.... add upto
.
.
.
..
if(vMaxYearMaxMonth = 11,
if(vMaxYearMaxMonth = 12
)))
I have attached the Solution might help you
May b this will be helpful
Also if you need to work dynamic for Month Selction just use
{1}
so expression will change to
Aggr(above( sum( {1}BookSize_Debtor_AR_POS),3),YearMonth)
Hi Rohit,
It works well for now. let me check for the original data.
thanks fella