Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Calculation YTD by month selected

Hi Guys,

I need your help..

How to write script, if I want to calculate YTD by month selected.

for example:

if I selected year 2014 and month may then accumulation (Jun13 - May14)

or if I selected year 2014 and month apr then accumulation (May13 - Apr 14)

or if I selected year 2013 and month jun then accumulation (Jul12 - Jun13)

Please help me..

thanks & regards..

1 Solution

Accepted Solutions
Not applicable

Re: Calculation YTD by month selected

Hi Jagan,

I was try your script, but the numbers accumulate all previous year 2013-2009.

and

I have a script :

sum({<DATE={">$(=addmonths(DATE,-24)) <=$(=addmonths(DATE,-12))"}, YEAR=, MONTH=>} AMOUNT

I think the script is true, because when I checked this script accumulate 12 months of Previous Year.

Btw, Thanks Sir Jagan...

Regards..

24 Replies
senpradip007
Valued Contributor III

Re: Calculation YTD by month selected

Try somthig like in Expressions:

Sum({<Date={">=$(=AddMonths(Max(Date),-11)) <=$(=Max(Date))"} >} [Measure Field] )

Not applicable

Re: Calculation YTD by month selected

Hi Valdi

You can try this

=SUM({<Year={'$(vMaxYear)'},Date={'<=$(vMaxDate)'},Month=,Day=,MonthYear=>} MeasureField)

vMaxYear = max(Year)

vMaxDate = max(Date)

Regards

Rishi Beri

senpradip007
Valued Contributor III

Re: Calculation YTD by month selected

Hi Rishi.

If you set Year as Max(Year) how do you get Previous Year data. This expression calculate data of a perticular year to till date.

Ex if you select year 2014 and month Jun then you would get data of 01-Jan-14 to 30-Jun-14. but the Valdi's requirement is different what you have thought.

Not applicable

Re: Calculation YTD by month selected

Hi Pradip

For previous year calculation you can use

=SUM({<Year={'$(vPriorYear)'},Month=,Day=,Date={'<=$(vPriorYearDate)'},MonthYear=>} MeasureField)

vMaxYear = max(Year)

vPriorYear = vMaxYear -1

vPriorYearDate = date(addyears(max(Date), -1), 'DD MMM YYYY')

Regards

Rishi Beri

Not applicable

Re: Calculation YTD by month selected

Hi Pradip,

I'v tried your script, but only accumulate 1 month not 12 months,

sum({Date= <--- Date or Months?

Thanks & Regards.

senpradip007
Valued Contributor III

Re: Calculation YTD by month selected

I thing you have just misunderstood the actual requirement. Valdi  wants if anyone selects year 2014 and month May then accumulation should be from Jun13 to May14.

Did you get the desired result by using following expressions what you have suggested?

=SUM({<Year={'$(vMaxYear)'},Date={'<=$(vMaxDate)'},Month=,Day=,MonthYear=>} MeasureField)

vMaxYear = max(Year)

vMaxDate = max(Date)

MVP & Luminary
MVP & Luminary

Re: Calculation YTD by month selected

Hi,

Try this expression

=Sum({<Year=, Month=, Date={">=$(=MonthStart(Max(Date),-12)) <=$(=Max(Date))"} >} [Measure Field] )


Replace Year=, Month=, Date= with your actual field names.


Regards,

Jagan.





Not applicable

Re: Calculation YTD by month selected

Hi Jagan,,

I've tried your script, but the result is the difference..

senpradip007
Valued Contributor III

Re: Calculation YTD by month selected

Can you share sample qvw?