Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set Analysis - YTD & MTD

Hi

Below is a sample Sales data.  Actuals Sales is up to Sep 2017, however Budgeted Sales goes up to June 2020.

How do I create an expression so that the Max Financial Year and Max Month is current (with so selections made)

So for example, its currently Sept 2017.  So,

  • Financial Year = 2018
  • Month = Sep 2017
  • Period_ID = 15

and say if currently it is  Nov 2018, then

  • Financial Year =  2019
  • Month = Nov 2018
  • Period_ID = 29

Thank you

Kind regards

Nayan

     

YearMonthMonthPeriod_IDFinancial YearActual SalesBudgeted Sales
201607Jul12017600400
201608Aug22017500200
201609Sep32017500900
201610Oct42017400800
201611Nov520173001000
201612Dec620171000900
201701Jan720171000200
201702Feb82017600200
201703Mar92017400800
201704Apr102017600100
201705May112017500900
201706Jun122017800700
201707Jul132018200300
201708Aug142018100600
201709Sep152018100600
201710Oct162018 300
201711Nov172018 900
201712Dec182018 500
201801Jan192018 400
201802Feb202018 900
201803Mar212018 600
201804Apr222018 800
201805May232018 700
201806Jun242018 600
201807Jul252019 300
201808Aug262019 400
201809Sep272019 400
201810Oct282019 500
201811Nov292019 100
201812Dec302019 700
201901Jan312019 500
201902Feb322019 200
201903Mar332019 200
201904Apr342019 200
201905May352019 600
201906Jun362019 200
201907Jul372020 900
201908Aug382020 900
201909Sep392020 500
201910Oct402020 300
201911Nov412020 800
201912Dec422020 800
202001Jan432020 500
202002Feb442020 900
202003Mar452020 100
202004Apr462020 800
202005May472020 100
202006Jun482020 600
9 Replies
prat1507
Specialist
Specialist

You can use YearMonth={'$(=date(Today(),'YYYYMM'))'}

in your set analysis.


Regards
Pratyush

Anonymous
Not applicable
Author

Hi Pratyush

Thank you for your reply.  Will try it out and let you know.

Kind regards

Nayan

Anonymous
Not applicable
Author

Hi Pratyush

I get the MTD value, but how do I get the Financial Year value?

Kind regards

Nayan

prat1507
Specialist
Specialist

You can use a similar expression as

FinancialYear={'$(=if(month(Today())>=4,Year(Today())+1,Year(Today())))'}

Regards

Pratyush

Anonymous
Not applicable
Author

Hi Pratyush

Thank you for your reply.  Your formula works but only if it is today.

Kind regards

Nayan

prat1507
Specialist
Specialist

You can specify an date in place of today. You can get that from a field, since you wanted the result to be based on current date, I used today().

Let me know if you need something else.

Regards

Pratyush

Anonymous
Not applicable
Author

ok, Thanks.

Vaibhav_Pathak
Contributor II
Contributor II

Hi Pratyush,

Can you explain how can we find data for a month before MTD. Like its jan 24 right now so in MTD we get jan 24 data, Now i want Dec 23 data.(MTD - 1).

Kindly help if you know.

Thanks,

Vaibhav

Vaibhav_Pathak
Contributor II
Contributor II

If anyone knows the solution kindly help me with this.