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: 
Anonymous
Not applicable

YoY calculation

Hi,

I am using year as date field. I want to calculate previous year's value associated with some dimension.

I am using this expression

if(Year='2014',Sum({<YearField = {'$(=Year(Today()))'}>} sales),Sum({<YearField = {'$(=Year(Today())-1)'}>} sales))

I have years starting from 2014 that is why if .

But, i am still getting current year values .

Please let me know what corrections to make.

However, to calculate it for current year using

Sum({<YearField = {'$(=Year(Today()))'}>} TAM_VALUE)  is giving correct results.

lech_miszkiewicz

stevedark

mto

20 Replies
Anonymous
Not applicable
Author

thanks for your reply.

I want to calculate it for every year starting from 2014 till 2020 to put it in a table as shown above.

Thank you .

MK9885
Master II
Master II

Convert that straight table into Pivot table, use Year dimension as Column.

It would be easier if you could provide us  sample data in XL?

Sorry I do not have Qlik View full version so only data would work

Anonymous
Not applicable
Author

YearIndustryYoYPYCYMeasure
2014Communications, Media & Entertainment--7554.785767565.762301
2014Consumer Industries & Retail--5434.2997096434.299709
2014Energy--5867.6278065762.627806
2014Financial Services--82990.8479412500.84794
2014Government--19064.4940113064.49401
2014Healthcare--9858.9971765658.997176
2014Manufacturing--6853.380796853.38079
2014Other--1990.6154591990.615459
2014Transportation--3490.4820963090.482096
2015Communications, Media & Entertainment--7650.8055227980.805522
2015Consumer Industries & Retail--6814.4997316814.499731
2015Energy--6085.4640416085.464041
2015Financial Services--13155.1734213155.17342
2015Government--13582.9344113582.93441
2015Healthcare--6194.2868626194.286862
2015Manufacturing--7153.3318127153.331812
2015Other--1977.2886331977.288633
2015Transportation--3379.0639213379.063921
2016Communications, Media & Entertainment--8963.8482498963.848249
2016Consumer Industries & Retail--7721.5363887721.536388
2016Energy--6982.0138386982.013838
MK9885
Master II
Master II

Please put your raw data into an XL file and attach it...

Anonymous
Not applicable
Author

I dont see any option to attach an excel file.

MK9885
Master II
Master II

When you click reply, on top right you'll see 'Use Advance editor'

Click than and upload your data...

With whats your sample expression and expected output...

Thanks.

Anonymous
Not applicable
Author

I am trying to find that option but I am unable to see it. I just have an option to add image or media, links .

Thanks.

trdandamudi
Master II
Master II

Don't reply from within your Inbox, because you will not see that option. Click Reply from outside the Inbox and you will see the below screen with the option "Use advance editor" and when you click on this you can attach a file:

juraj_misina
Luminary Alumni
Luminary Alumni

Hi Ronit,

I think what you are trying to do is to manipulate dimension value with set analysis, which is not possible. Set analysis is evaluated before a chart is calculated. So this means that you are not able to get data from previous year on this year's row (because intersection of data set for, say current year 2018 and data for previous year 2017 is an empty set, hence no data to aggregate).

There could be a way to achieve this using the Above() function, but that's a tricky one.

Hope this helps.

Juraj

Anonymous
Not applicable
Author

Yea thats why I was putting if condition so that when it selects 2014 the previous year should be 2014 and for 2014 onwads it should be one less .

my data set only has ----- Year, Industry as Dimensions

and value - as measure

CY and PY are calculated fields