Previous year-month and Previous Year-QTD calculation

[AH]

I am trying to calculate Current year & previous year amount. Please take a look at the calculation and suggest. I would really appreciate your help.

=num((Sum({<SalesItemID={'A','B'},[Invoice Year] = {'$(=Max([Invoice Year]))'}>}[Invoice Units])),'##,##0')----Current Year. which is 2015 now

=num((Sum({<SalesItemID={'A','B'},[Invoice Year] = {'$(=Max([Invoice Year]-1))'}>}[Invoice Units])),'##,##0')----Previous Year. Which is for my case 2014

Now I want to calculate Current month for 2015 which is,
=num((Sum({<SalesItemID={'A','B'},[Invoice Month] = {'$(=Max([Invoice Month]))'}>}[Invoice Units])),'##,##0')----Current month for 2015

What would be the calculation for Current month for 2014?

Also,

I would like to calculate Quarter to date for Year 2015 which is,
=num((Sum({<SalesItemID={'A','B'},[Invoice Quarter] = {'$(=Max([Invoice Quarter]))'}>}[Invoice Units])),'##,##0')----Quarter to date for 2015

What would be the calculation for Quarter To Date for Previous year 2014? Mean the same quarter as of 2015 for the previous year.

Could you please give me some feedback?

Also comment on calculation i have mentioned here.

[Gysbert_Wassenaar · Accepted solution]

Current month of last year:

=num((Sum({<SalesItemID={'A','B'},[Invoice Year] = {'$(=Max([Invoice Year]-1))'},[Invoice Month] = {'$(=Max([Invoice Month]))'}>}[Invoice Units])),'##,##0')

[AH]

Thanks Gysbert! I really appreciate your quick help.

Shan

[AH]

Hi Gysbert,

This Two expressions are giving me the same value. But they are same month from different year.

Current Month(March) for Year 2015:
=num((Sum({<SalesItemID={'A','B'},[Invoice Month] = {'$(=Max([Invoice Month]))'}>}[Invoice Units])),'##,##0')

Current Month(March) for Year 2014:
=num((Sum({<SalesItemID={'A','B'},[Invoice Year] = {'$(=Max([Invoice Year]-1))'},[Invoice Month] = {'$(=Max([Invoice Month]))'}>}[Invoice Units])),'##,##0')

What could possibbly go wrong here?

Could you please have a look?

Thanks,
Shan

[Gysbert_Wassenaar]

Can you post a qlikview document that demonstrates the problem?

[AH]

Hi Gysbert,

Please find attach file and let me what you think.

Thanks,

Shan

[Gysbert_Wassenaar]

Ok, I assumed you were selecting a year and month first. That's not the case for the first two text boxes. Try these expressions:

Current Month previous Year:

=num((Sum({<SalesItemID={'A', 'B'},[Invoice Year] = {'$(=Max([Invoice Year]-1))'},[Invoice Month] = {'$(=Max({<[Invoice Year] = {"$(=Max([Invoice Year]))"}>}[Invoice Month]))'}>}[Invoice Units])),'##,##0')

Current Month current Year:

=num((Sum({<SalesItemID={'A', 'B'},[Invoice Year] = {'$(=Max([Invoice Year]))'},[Invoice Month] = {'$(=Max({<[Invoice Year] = {"$(=Max([Invoice Year]))"}>} [Invoice Month]))'}>}[Invoice Units])),'##,##0')

Selected Month and selected Year:

=num((Sum({<SalesItemID={'A', 'B'},[Invoice Year] = {'$(=Max([Invoice Year]))'},[Invoice Month] = {'$(=Max([Invoice Month]))'}>}[Invoice Units])),'##,##0')

See attached qvw.

[AH]

Hi Gysbert,

Thank you very much for the very helpful feedback.

Its working fine with the current month.

But, For the Current Quarter Previous year and Current Quarter Current year the values are giving me zero with below expressions.

Current Quarter Previous Year:
=num((Sum({<SalesItemID={'A','B'},[Invoice Year] = {'$(=Max([Invoice Year]-1))'},[Invoice Quarter] = {'$(=Max({<[Invoice Year] = {"$(=Max([Invoice Year]))"}>}[Invoice Quarter]))'}>}[Invoice Units])),'##,##0')

Current Quarter Current Year:
=num((Sum({<SalesItemID={'A','B'},[Invoice Year] = {'$(=Max([Invoice Year]))'},[Invoice Quarter] = {'$(=Max({<[Invoice Year] = {"$(=Max([Invoice Year]))"}>} [Invoice Quarter]))'}>}[Invoice Units])),'##,##0')

Is that because the the Quarter hasnt finished yet for the current year and also for the previous year the Quarter didnt finish that time in 2014?

Thanks,

Shan

[Gysbert_Wassenaar]

Your 2015 expression contains SalesItemID={'CELLEX'}, but your document does not contain that sales item.

Your quarters are text values, not numbers, so the max function doesn't work. You can use the maxstring function instead or create a dual value quarter in the script: dual( 'Q' & ceil([Invoice Month]/3),  ceil([Invoice Month]/3)) as [Invoice Quarter]

[AH]

I Change the calendar and based on the invoice date and made the Quarter based on the date.

Do I need to use the dual function here?

Thanks,

Shan