Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
mto
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 .
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
Year | Industry | YoY | PY | CY | Measure |
2014 | Communications, Media & Entertainment | - | - | 7554.78576 | 7565.762301 |
2014 | Consumer Industries & Retail | - | - | 5434.299709 | 6434.299709 |
2014 | Energy | - | - | 5867.627806 | 5762.627806 |
2014 | Financial Services | - | - | 82990.84794 | 12500.84794 |
2014 | Government | - | - | 19064.49401 | 13064.49401 |
2014 | Healthcare | - | - | 9858.997176 | 5658.997176 |
2014 | Manufacturing | - | - | 6853.38079 | 6853.38079 |
2014 | Other | - | - | 1990.615459 | 1990.615459 |
2014 | Transportation | - | - | 3490.482096 | 3090.482096 |
2015 | Communications, Media & Entertainment | - | - | 7650.805522 | 7980.805522 |
2015 | Consumer Industries & Retail | - | - | 6814.499731 | 6814.499731 |
2015 | Energy | - | - | 6085.464041 | 6085.464041 |
2015 | Financial Services | - | - | 13155.17342 | 13155.17342 |
2015 | Government | - | - | 13582.93441 | 13582.93441 |
2015 | Healthcare | - | - | 6194.286862 | 6194.286862 |
2015 | Manufacturing | - | - | 7153.331812 | 7153.331812 |
2015 | Other | - | - | 1977.288633 | 1977.288633 |
2015 | Transportation | - | - | 3379.063921 | 3379.063921 |
2016 | Communications, Media & Entertainment | - | - | 8963.848249 | 8963.848249 |
2016 | Consumer Industries & Retail | - | - | 7721.536388 | 7721.536388 |
2016 | Energy | - | - | 6982.013838 | 6982.013838 |
Please put your raw data into an XL file and attach it...
I dont see any option to attach an excel file.
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.
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.
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:
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
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