Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I just cannot figure out the syntax and hope someone can help.
I want to display a table like this (simple version):
year, value, change from last year
2012, 10, -
2013, 11, 10%
2014, 12.1, 10%
2015, 24.2, 100%
I need an expression for the 3rd column.
The actual data is more complicated (it is year to date and there are other dimensions), but the expression should be something like this. (Current Year - Last Year)/Last Year
count(DISTINCT {<CountFlag={'1'},MonthNum={"<=$(=$(vCYTDMon))"},Month=,Year=,Quarter=,ReportingDate=,WeekStart=,Mode={'yyz'}>} Account)
-
count(DISTINCT {<CountFlag={'1'},MonthNum={"<=$(=$(vCYTDMon))"},Month=,Year={'(Year-1)'},Quarter=,ReportingDate=,WeekStart=,Mode={'yyz'}>} Account)
)
/
count(DISTINCT {<CountFlag={'1'},MonthNum={"<=$(=$(vCYTDMon))"},Month=,Year={'(Year-1)'},Quarter=,ReportingDate=,WeekStart=,Mode={'yyz'}>} Account)
How do I get that Year-1 concept into the set?
I have also tried the Above() function, but I need this more precise approach to work.
I think you can combine Above() with the AGGR() function, to overcome the challenge of incorrect sorting. Something like this:
sum(
AGGR(
above(Sum(Sales)),
OtherField, Year)
)
This AGGR function will sort the data by OtherFIeld first, and Year next. Make sure that the years are sorted properly in the data. If they are not, you can sort them within AGGR() :
sum(
AGGR(
above(Sum(Sales)),
OtherField,
(Year, NUMERIC, ASCENDING)
)
)
I'm quoting from memory, so double check the syntax.
AGGR() is a powerful function, and it's relatively little-understood among developers. I'm teaching it with a lot of details at the Masters Summit for Qlik - see if our agenda is right for you.
cheers,
Oleg Troyansky
Hi David,
A few comments:
- If you can use the Above() function for your calculation, it would be the easiest solution.
- You can't use Set Analysis in this chart (with the field Year being a Dimension) because Set Analysis conditions are only validated once, before the chart is even calculated, therefore they can't be sensitive to the values of your Dimensions (such as Year).
- In order to overcome the previous issue, developers often create a so called "As of Date" table - where the single Date field is being split into two fields - "Display Date" and "Transaction Date". The "As of Date" table contains both dates and a set of flags that determine the relationship between the two - for example, CYTD_Flag, PYTD_Flag, etc...
You can find several free white papers describing the concept of the "As of Date" table, including my blog article here:
QlikView Blog Q-Tip #4 How to Use "As of Date" table | Natural Synergies
You can also read a more detailed explanation with several hands-on exercises in my book QlikView Your Business.
Finally, you can learn this technique, along with many other advanced Qlik techniques, at the Masters Summit for Qlik.
Cheers,
Oleg Troyansky
thank you; helpful to rule this out. I will go back to "above" function. Problem I have is that the data is like
year, other field, value
where other field is 2nd dimension. So "above" needs to point to several rows above the current row. I've tried using the offset, but it did not work
frustratingly, the above function will not do what I need. The data is sorted by year, otherfield. Above would work if the sort order were reversed, but that defeats the point of the presentation.
I think you can combine Above() with the AGGR() function, to overcome the challenge of incorrect sorting. Something like this:
sum(
AGGR(
above(Sum(Sales)),
OtherField, Year)
)
This AGGR function will sort the data by OtherFIeld first, and Year next. Make sure that the years are sorted properly in the data. If they are not, you can sort them within AGGR() :
sum(
AGGR(
above(Sum(Sales)),
OtherField,
(Year, NUMERIC, ASCENDING)
)
)
I'm quoting from memory, so double check the syntax.
AGGR() is a powerful function, and it's relatively little-understood among developers. I'm teaching it with a lot of details at the Masters Summit for Qlik - see if our agenda is right for you.
cheers,
Oleg Troyansky
this appears to work!