topic Re: Conditional display of text boxes based on the number of years in a bar chart in QlikView

Conditional display of text boxes based on the number of years in a bar chart

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a bar chart displaying 3 years of data 2014,2015 and 2016. And YOY% are displayed between the bars in a text box.

Condition here is when there is data for all the 3 years then only text boxes has to be displayed.

See below screenshot

When data is there only for 1 or 2 years the text boxes should not be displayed.

To achieve this I wrote a conditional show statement under Layout 'count(DISTINCT(Year)) >2'. But this is not hiding the text boxes.

If you see in below screenshot, textboxes are overlapping with the bars.

Note: There are no year filters available in the dashboard

Can someone please help me hide these textboxes when 3 years of data is not available.

Thanks!

Re: Conditional display of text boxes based on the number of years in a bar chart

Anonymous — Wed, 06 Apr 2016 18:12:05 GMT

can you please share your sample QVW file. May be that would give us more information about the app.

Re: Conditional display of text boxes based on the number of years in a bar chart

Anonymous — Wed, 06 Apr 2016 19:24:33 GMT

Attached the qvw...
Please take a look.

Re: Conditional display of text boxes based on the number of years in a bar chart

sunny_talwar — Wed, 06 Apr 2016 19:56:57 GMT

Try this expression:

GetSelectedCount([Parent Firm Name]) >=1 and

Count(Aggr(if(sum({<SPENDTYPE = {'ARIBA'}>}(SPEND)/1000000)-(sum({<SPENDTYPE = {'OTHER_GL','PAYMENTS'}>}(SPEND)/1000000)) > 0,

(sum({<SPENDTYPE = {'ARIBA'}>}(SPEND)/1000000))-(sum({<SPENDTYPE = {'OTHER_GL','PAYMENTS'}>}(SPEND)/1000000))), Year)) >= 3

Re: Conditional display of text boxes based on the number of years in a bar chart

Anonymous — Wed, 06 Apr 2016 20:21:51 GMT

Thanks for your reply Sunny.

With the expression you gave, text box is hidden all the time irrespective of the number of years

Re: Conditional display of text boxes based on the number of years in a bar chart

sunny_talwar — Wed, 06 Apr 2016 20:24:47 GMT

Can you share the application with the three bars? I cannot test that part because it was not available in the previous qvw file

Re: Conditional display of text boxes based on the number of years in a bar chart

Anonymous — Wed, 06 Apr 2016 20:39:57 GMT

Added new qvw to my original post..

I included 2 firms, one with 3 years of data and the other is with2 years.

Please take a look.

Re: Conditional display of text boxes based on the number of years in a bar chart

sunny_talwar — Wed, 06 Apr 2016 21:06:16 GMT

Give this a try:

GetSelectedCount([Parent Firm Name]) >=1 and

Count(DISTINCT Aggr(if(

RangeSum(

Sum({<[Matter #] = P(MATTER_NO), SPENDTYPE -= {'ARIBA'}>}SPEND/1000000),

Sum(OUTLOOK_SPEND/1000000),

Sum(OUTLOOK_SPEND/1000000)*12) > 0, Year), Year, CHECKDATE)) >= 3

Re: Conditional display of text boxes based on the number of years in a bar chart

Anonymous — Wed, 06 Apr 2016 21:59:02 GMT

Thanks much! That worked!

Unfortunately users changed the requirement They want to see YOY% if there are 2 or more years in the bar chart.

example: in below screenshot they want to see YOY% between 2014 and 2015 and -100% should not be displayed; -40% which is the YOY% for 2014 and 2015 should not overlap with the bars.

Can you please help me with this. I really appreciate your help.

Thanks a lot!

Re: Conditional display of text boxes based on the number of years in a bar chart

sunny_talwar — Wed, 06 Apr 2016 22:17:06 GMT

So you need three text boxes now? 2 show up when you have 3 years and 1 show up when you have 2 years?

Re: Conditional display of text boxes based on the number of years in a bar chart

Anonymous — Wed, 06 Apr 2016 22:21:00 GMT

‌yes please..

Re: Conditional display of text boxes based on the number of years in a bar chart

sunny_talwar — Wed, 06 Apr 2016 22:25:04 GMT

Can you check:

Re: Conditional display of text boxes based on the number of years in a bar chart

Anonymous — Wed, 06 Apr 2016 23:43:14 GMT

Thanks much!

One last thing, can you please help me write an expression for the below situation.

Display 1 text box when 2 alternate years data is available.

Thanks a lot for your time!

Re: Conditional display of text boxes based on the number of years in a bar chart

sunny_talwar — Wed, 06 Apr 2016 23:45:37 GMT

Can you share a sample where this is true?

Re: Conditional display of text boxes based on the number of years in a bar chart

Anonymous — Thu, 07 Apr 2016 00:03:40 GMT

Attached qvw to my original post.

Re: Conditional display of text boxes based on the number of years in a bar chart

sunny_talwar — Thu, 07 Apr 2016 00:21:14 GMT

The text box seems to show up, but the number isn't showing

Re: Conditional display of text boxes based on the number of years in a bar chart

Anonymous — Thu, 07 Apr 2016 00:31:42 GMT

I will check that.

Thanks again for all your help!

Re: Conditional display of text boxes based on the number of years in a bar chart

Anonymous — Thu, 07 Apr 2016 19:16:52 GMT

Hi Sunny,

I need your help with the same issue. I have attached the latest qvw to my original post.

Issue: For this firm 2014-2015 YOY% text box is overlapping with the bar and YOY% is not displayed for 2015-2016 YOY%. I tried to modify the expressions you wrote for conditional show but no luck.

Can you please look into this... Thanks!

Re: Conditional display of text boxes based on the number of years in a bar chart

sunny_talwar — Thu, 07 Apr 2016 19:50:03 GMT

Hopefully no more changes will be required. Check out the attached

Re: Conditional display of text boxes based on the number of years in a bar chart

Anonymous — Thu, 07 Apr 2016 20:06:11 GMT

Hopefully not

Thanks much for your quick reply!