Previous FY to same date of today last year

[wassimharfoush]

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..

[marcus_sommer]

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

[wassimharfoush]

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...

[jagan]

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.

[wassimharfoush]

thank you but this gives me 0 .. the variables work fine when tested in text boxes though but the expression returns 0

[wassimharfoush]

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)

[wassimharfoush]

Any suggestions?

[jagan · Accepted solution]

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.

[settu_periasamy]

Hi,

Check the Attachment.

You can try below expression (give the '=' sign in your variable)

=Sum({$<GlobalDate={">=$(vLastFiscalYearStart)<=$(vLastYearDay)"}>}Value)

[wassimharfoush]

Thank you that was helpful.