Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have got a dummy data that I have created for checking certain functionality in qlikview, but am not sure what is the best way to achieve it.
So, I have some information about the schools - classes and the yearly number of excellent or good grades (see attached file). For each year and for each class in the school I perform a comparison between excellent and good grades. If there are more excellent grades, then the flag is yes and '0' - otherwise. This works fine.
But now I want to see if there is the case, when a class had a yes - flag for at least two consecutive years. And if it is the case, then just in additional column add TRUE for the whole class.
I understand that the example doesn't make any sense, but I am not able to disclose original data.
Thanks for any suggestions
May be try this
If(SubStringCount(Concat(TOTAL <School, Class> Aggr(If(Excellent > Good, 'yes', 0), School, Class, Year), '|'), 'yes') >= 2, 'TRUE')
Or this
If(SubStringCount(Concat(TOTAL <School, Class> Aggr(If(Excellent > Good and Above(Excellent) > Above(Good) and Year = Above(Year) + 1, 'yes', 0), School, Class, (Year, (NUMERIC))), '|'), 'yes') >= 1, 'TRUE')
The above expression will only work in QV 12 or above..... because of this new feature that came in QV12