I want to create a bar chart which would show a variance of target vs last day, in it i am facing a problem to get balance of previous transactional date.
The scenario is if i am standing on 3 january 2012, showing me some balance and 2 and 1 januray were holidays so they did not have any balance, so i should be having the balance of 31 december 2011 as it has the balance data, but i dont know the experssion for how to get the balance of 31 december 2011. The expression should get the previous date data and previous date could be any previous date having balance.
attached please find a sample file I have created for previous date selection.
You could see 2 text objects, 1 at center is for calculating max dates and the second below is calculating the previous date. so if I choose 3 Jan 2011, than this text object would show me the previous date which is 31 DEC 2010. if I choose 1 Feb 2011, the text object would show me 3 Jan 2011.
I thought I'll simplify this expression and I have used FirstSortedValue() to simply. This is much simplier and reliable approach compared to first method. Here is the expression and I'm also attaching the QV document for your reference.
Thanks a lot D V your expression of maxstring works like a charm and also firstsortedvalue was another option for it.
Now I am having another issue now to find the monthly Transactional period end Balance;
I need to show balance on month Transactional period end, so how to showit, because I tried to show monthly balance with every month’s end datebalance. Now the scenario is I want to show a line chart, which would berunning on month dimension and it shows the balance of each months end date.Like
Month: MAX(Date)of that month: Balance
Jan 31 2000
Feb 28 1500
Mar 30 1400
April 21 1800
Your help me in this issue would be highly appreciated.
Ahmed - Good morning. Not sure if I have understood your previous question. Do you want to show monthly closing balance for each month? I mean your dimension would be Month and your expression would be MaxString(Sales). If you want something else then please can you explain in detail? Probably with some example like your original question.