Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have used the following expression to get YTD current FY sales and it works ok.
Sum({$<Date={"=<$(=$(vToday))"}, Date={">=$(=$(vFiscalYearStart))"}>}SALE)
The variables defined as the following:
vToday: date(today())
vFiscalYearStart: if(today() < Date('7/1/'&Year(Today())),'7/1/' &(Year(Today())-1),'7/1/'&(Year(Today())))
But when I used the following expression for the same period last FY it did not work.
Sum({$<Date={"=<$(=$(vLastYearDay))"}, Date={">=$(=$(vLastFiscalYearStart))"}>})
The variable defined as the following:
vLastFiscalYearStart:
if(today() < Date('7/1/'&Year(Today())-1),'7/1/' &(Year(Today())-2),'7/1/'&(Year(Today())-1))
vLastYearDay: Date(AddYears(Today(), -1))
Please kindly help..
Hi,
If you have any date related fields exclude its selection by using below expression
Sum({$<MonthFieldName=, YearFieldName=, QuarterFieldName=, Date={">=$(=vLastFiscalYearStart)<$(=vLastYearDay)"}>} MeasureName)
Check this link
Set Analysis for certain Point in Time
Hope it helps you.
Regards,
Jagan.
The reason could be that you checked the LastFiscalYear variable against today() like by FiscalYear, try instead:
vLastFiscalYearStart:
if(Date(AddYears(Today(), -1)) < Date('7/1/'&Year(Today())-1),'7/1/' &(Year(Today())-2),'7/1/'&(Year(Today())-1))
If this not worked use a pivot-table without an expression-name for these expression and you will see by hovering over the expression-label how qlikview interpreted your expression. This will give you valuable hints what needs to be adjusted.
- Marcus
Thank you for your help.. this is useful info but still doesn't fix my problem. when I follow your method and put the expression with no title I see the right dates carried over but the numbers still off.
I have put the expression into a straight table along with the date. and I noticed I see all the dates from the FY start of previous year up until the latest date...
Hi,
Try like this
vLastYearDay: Date(AddYears(Today(), -1))
vLastFiscalYearStart : if(vLastYearDay < Date('7/1/'&(Year(Today())-1)),'7/1/' &(Year(Today())-2),'7/1/'&(Year(Today())-1))
Sum({$<Date={">=$(=vLastFiscalYearStart)<$(=vLastYearDay)"}>} MeasureName)
Hope this helps you.
Regards,
Jagan.
thank you but this gives me 0 .. the variables work fine when tested in text boxes though but the expression returns 0
Here is a qvw sample, The starting date works ok but the ending date should be 12/1/14
I need an expression return the values between 7/1/14 (Previous FY Start Date) and ends 12/16/14.(same day of today from last year)
Any suggestions?
Hi,
If you have any date related fields exclude its selection by using below expression
Sum({$<MonthFieldName=, YearFieldName=, QuarterFieldName=, Date={">=$(=vLastFiscalYearStart)<$(=vLastYearDay)"}>} MeasureName)
Check this link
Set Analysis for certain Point in Time
Hope it helps you.
Regards,
Jagan.
Hi,
Check the Attachment.
You can try below expression (give the '=' sign in your variable)
=Sum({$<GlobalDate={">=$(vLastFiscalYearStart)<=$(vLastYearDay)"}>}Value)
Thank you that was helpful.