Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I'm trying to complete the requirement on the attached excel.
Any help would be greatly appreciated!
Thanks in Advance
J
use CTRL+ALT+V.
1.click on Add
2. type the variable name
3. click on variable created and put the expression in the definition box.
if your problem is resolved then make this thread Answered.
Thanks
Sushil
Hi,
Please find the attached sample app.
Hope that helps you.
HTH
Sushil
Hi,
Try this Expression.
=(sum({<FinalDate = {">=$(=date(Yearstart(max(FinalDate))))<=$(=max(FinalDate))"}, Type={ 'STUB'},month=>}[Net Sales]))
Regards,
Nirav Bhimani
Table1:
LOAD Type,
FinalDate,
year(FinalDate) * 100 + month(FinalDate) as yearmonth,
[Net Sales],
year(AddMonths(FinalDate,-1))*100 + month(AddMonths(FinalDate,-1)) as previousyearmonth
FROM
(
Left Join
LOAD
Type,
yearmonth as previousyearmonth,
max(FinalDate) as maxPrev
Resident Table1
group by Type, yearmonth;
This code consider previous month only as you need ...
Hope it helps
Hi Nirav: Thanks for your time and attempt. However this did not work...... 😞
Thanks Sushil, However the formula would work only on a one-off basis. For example if I was to select another date say 15/02/13, the previous month would then be January. I would need the figures to automatically change to the max date in Jnaury without having to amend the formula.....
If u checked the app.. i think it is working fine. as u have another filter type='STUB'
then when u are selecting 2/15/2013 it is returning 6 which is value of 1/25/2013.
Please see the app..
Thanks Sushil: So you say If I create the 3 variables and retest on your app it should work?
Let me explain you solution logic;
1. first variable will hold monthstartdate of previous month
2. Second variable will hold the monthenddate of previous month
3. Third vaiable will hold the max date of the previous month date range
and then finally you can have sum of max date of previous month.
I used variable for this for simplicity and understanding..
Feel free if u still have confusion.
Thanks Sushil, This makes perect sense! Thank You!
However: Not sure how to creaste & SAVE a variable?