topic Re: Dynamic field name based on dataset but ignoring filter in QlikView

Dynamic field name based on dataset but ignoring filter

stevenkasian — Fri, 05 Feb 2016 02:44:44 GMT

Hello all, I am quite new in Qlikview and I have a question regarding to dynamic field name.

I want to have a dynamic field name based on my dataset.

=if(SubStringCount(Concat(QuarterYear, '|'), '4Q15'))>0, '2015 Revenue',

if(SubStringCount(Concat(QuarterYear, '|'), '3Q15'))>0,'YTQ 3Q15 Revenue',

if(SubStringCount(Concat(QuarterYear, '|'), '2Q15'))>0,'YTQ 2Q15 Revenue',

if(SubStringCount(Concat(QuarterYear, '|'), '1Q15'))>0,'YTQ 1Q15 Revenue',

'Null'))))

QuarterYear is the field in my dataset containing '3Q15', '2Q15', '1Q15'

I want a column name in a chart table to display 'YTQ 3Q15 Revenue' since my dataset only has '3Q15', '2Q15', '1Q15' now.

When I import new dataset having '4Q15','3Q15', '2Q15', '1Q15', the column name will display '2015 Revenue'.

It works, however, the column name also get changed based on filter. If I select 2Q15 in a list box, the column name will follow and change to 'YTQ 2Q15 Revenue'.

Could anyone help me on this, dynamic column name based on dataset but fix to filter?

Highly appreciated.

Message was edited by: Wing Fung Ng

Re: Dynamic field name based on dataset but ignoring filter

PradeepReddy — Fri, 05 Feb 2016 19:17:35 GMT

if u select the filter, the data will change accordingly and the label also inline to the data what we are showcasing.

If I understand correctly,

What ever u made the filter selections(QuarterYear), the data in chart should not change...

In that case for label use max(QuarterYear)

Expression : sum({<QuarterYear=>}Revenue)

Re: Dynamic field name based on dataset but ignoring filter

stevenkasian — Sat, 06 Feb 2016 01:15:43 GMT

Thanks for amusing my thought. Finally I got this. vYear3 is the variable I can set, like 2015, 2016, 2017. Generally I fixed the year as my preset variable vYear3, and below MAX function found the maximum quarter I have in the dataset disregard the filter.

'YTQ 'max( {<Year={$(vYear3)}, Quarter = >} left(Quarter,1))&'Q'&right(vYear3,2)

It returns 'YTQ 3Q15' if my dataset has 1Q,2Q,3Q data for 2015. No impact from any filters.

Thanks!!