Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kicchu465
Creator
Creator

YTD Calculation

Hi All,

I am new to qlik sense.

I have a requirement to calculate YTD.

I have two Fields

1.LINK_FISCAL_PERIOD_DATE

2.LINK_FISCAL_PERIOD

The Link_Fiscal_Period has values from 2018001/2018002/2018003 and so on....

The LINK_FISCAL_PERIOD_DATE has values from 'Apr-2017'/May-2017/Jun-2017 and so on..

I wrote an expression to calculate YTD as below but expression is not resulting any values.

=sum({<LINK_FISCAL_PERIOD_DATE ={">=$(=Date(AddYears(YearStart(max(makedate(left(LINK_FISCAL_PERIOD,4),right(LINK_FISCAL_PERIOD,2))),0,4),-1),'MMM-YYYY'))<=$(=date(Addmonths(Max(makedate(left(LINK_FISCAL_PERIOD,4),right(LINK_FISCAL_PERIOD,2))),-9),'MMM-YYYY'))"},[Cost Type] = {'Total Opex'}>}AMOUNT_COA_CRCY_SIGNS)/1000

Could you please check and let me know is there any issue with expression or do the expression requires any tweaks to get the desired values.

Note : LINK_FISCAL_PERIOD date format is CCYY0MM

The Mapping of these fields are as below:

if(FISCAL_YEAR_PERIOD = '2018001', 'Apr-2017',

if(FISCAL_YEAR_PERIOD = '2018002', 'May-2017',

    if(FISCAL_YEAR_PERIOD = '2018003', 'Jun-2017',

        if(FISCAL_YEAR_PERIOD = '2018004', 'Jul-2017',

            if(FISCAL_YEAR_PERIOD = '2018005', 'Aug-2017',

                if(FISCAL_YEAR_PERIOD = '2018006', 'Sep-2017',

                    if(FISCAL_YEAR_PERIOD = '2018007', 'Oct-2017',

                        if(FISCAL_YEAR_PERIOD = '2018008', 'Nov-2017',

                            if(FISCAL_YEAR_PERIOD = '2018009', 'Dec-2017',

                                if(FISCAL_YEAR_PERIOD = '2018010', 'Jan-2018',

                                    if(FISCAL_YEAR_PERIOD = '2018011', 'Feb-2018',

                                        if(FISCAL_YEAR_PERIOD = '2018012', 'Mar-2018',

                                            if(FISCAL_YEAR_PERIOD = '2019001', 'Apr-2018',

                                                if(FISCAL_YEAR_PERIOD = '2019002', 'May-2018',

                                                    if(FISCAL_YEAR_PERIOD = '2019003', 'Jun-2018',

                                                        if(FISCAL_YEAR_PERIOD = '2019004', 'Jul-2018',

                                                            if(FISCAL_YEAR_PERIOD = '2019005', 'Aug-2018',

                                    if(FISCAL_YEAR_PERIOD = '2019006', 'Sep-2018',

                                        if(FISCAL_YEAR_PERIOD = '2019007', 'Oct-2018',

                                            if(FISCAL_YEAR_PERIOD = '2019008', 'Nov-2018',

                                                if(FISCAL_YEAR_PERIOD = '2019009', 'Dec-2018',

                                                    if(FISCAL_YEAR_PERIOD = '2019010', 'Jan-2019',

                                                        if(FISCAL_YEAR_PERIOD = '2019011', 'Feb-2019',

                                                                                        if(FISCAL_YEAR_PERIOD = '2019012', 'Mar-2019', 


Thanks,

S k

10 Replies
kicchu465
Creator
Creator
Author

Hi All,

Did anyone had a chance to look into above requirement

Thanks,

ChennaiahNallani
Creator III
Creator III

share sample qvf

kicchu465
Creator
Creator
Author

Hi,

I cant share the app as it is huge and the data is coming from different sources.

Thanks,

S k

kicchu465
Creator
Creator
Author

Hi All,

I have now a new requirement.

In the below script i have mappend the dates from data source to fiscal month year.

My fiscal year starts from apr to march.

In the below example when the date is 2018001 to 20180012 then i have mapped to Apr-2017 to Mar-2018 creating my fiscal year. I have hard coded the dates to form fiscal year. Is there way to make the script more dynamic rather than hardcode.

if(FISCAL_YEAR_PERIOD = '2018001', 'Apr-2017',

if(FISCAL_YEAR_PERIOD = '2018002', 'May-2017',

    if(FISCAL_YEAR_PERIOD = '2018003', 'Jun-2017',

        if(FISCAL_YEAR_PERIOD = '2018004', 'Jul-2017',

            if(FISCAL_YEAR_PERIOD = '2018005', 'Aug-2017',

                if(FISCAL_YEAR_PERIOD = '2018006', 'Sep-2017',

                    if(FISCAL_YEAR_PERIOD = '2018007', 'Oct-2017',

                        if(FISCAL_YEAR_PERIOD = '2018008', 'Nov-2017',

                            if(FISCAL_YEAR_PERIOD = '2018009', 'Dec-2017',

                                if(FISCAL_YEAR_PERIOD = '2018010', 'Jan-2018',

                                    if(FISCAL_YEAR_PERIOD = '2018011', 'Feb-2018',

                                        if(FISCAL_YEAR_PERIOD = '2018012', 'Mar-2018',

                                            if(FISCAL_YEAR_PERIOD = '2019001', 'Apr-2018',

                                                if(FISCAL_YEAR_PERIOD = '2019002', 'May-2018',

                                                    if(FISCAL_YEAR_PERIOD = '2019003', 'Jun-2018',

                                                        if(FISCAL_YEAR_PERIOD = '2019004', 'Jul-2018',

                                                            if(FISCAL_YEAR_PERIOD = '2019005', 'Aug-2018',

                                    if(FISCAL_YEAR_PERIOD = '2019006', 'Sep-2018',

                                        if(FISCAL_YEAR_PERIOD = '2019007', 'Oct-2018',

                                            if(FISCAL_YEAR_PERIOD = '2019008', 'Nov-2018',

                                                if(FISCAL_YEAR_PERIOD = '2019009', 'Dec-2018',

                                                    if(FISCAL_YEAR_PERIOD = '2019010', 'Jan-2019',

                                                        if(FISCAL_YEAR_PERIOD = '2019011', 'Feb-2019',

                                                                                        if(FISCAL_YEAR_PERIOD = '2019012', 'Mar-2019


Note : FISCAL_YEAR_PERIOD date format is YYYY0MM


I am not using master calendar in my app.


Thanks,

Sk

Chanty4u
MVP
MVP

felipedl‌  Can have any idea?

balabhaskarqlik

Modify the script from this:

https://community.qlik.com/docs/DOC-7094

And create expression like this:

Sum({$<[number]={'*SINV*'},[res_partner.name]={*}-{''},[date_invoice]={'>=$(YearStart($(CurrMonth),0,10))<=$(MonthEnd($(CurrMonth)))'}>}amount_untaxed)

kicchu465
Creator
Creator
Author

Hi Bhaskar,

Thanks.

Cant we make the script dynamic without master calander.

The date format that I have YYYY0MM.

kicchu465
Creator
Creator
Author

Hi All,

Any luck??

Thanks,

S k

felipedl
Partner - Specialist III
Partner - Specialist III

I would always have a number representation of the date on the table for this situation, since set analysis will be simpler with number rather than the string.

On your table, do the fiscal year and a numeric date and change the set analysis to:

Calendar:

Load

     Date,

     num(Date) as NumDate

From [Whatever];

And the expression:

=

sum

(

     {<

         // Ignore the selected date

          Date,

          LINK_FISCAL_PERIOD_DATE =

          {">=$(=Num(AddMonths(Max(NumDate),-9)))<=$(=Max(NumDate))"},

          [Cost Type] = {'Total Opex'}

     >}

     AMOUNT_COA_CRCY_SIGNS

)

/

1000