topic Dynamic Labels for bar chart in QlikView

Dynamic Labels for bar chart

apoorvasd — Sun, 22 Oct 2017 17:37:09 GMT

Hello Everyone,

I would like to have a dynamic labels for my chart. What I mean is, when I select a week or a month which is less than current month, then the label should display, "Thereof BB" and if the month or week selected is greater than or equal to current month then the label of the bar should display ,"Risk BB" . With the below mentioned expression I am getting to work this except that, when I select more than one past or future month, it is not working.

=if(GetFieldSelections(WeekShort)>=month(Date#(WeekShort,'MMM')) or Month(Date#(POPER_NEW, 'MMM'))>=month(today())

,'Risk BB' ,if(GetFieldSelections(WeekShort)<month(Date#(WeekShort,'MMM')) or Month(Date#(POPER_NEW, 'MMM'))<month(today()) ,'Thereof BB', 'Thereof BB'))

Any help on this?

Thank you.

Re: Dynamic Labels for bar chart

swuehl — Sun, 22 Oct 2017 18:44:10 GMT

I think the GetFieldSelections() is probably the part that creates the issue for you.

But I don't understand this part really, why are you comparing WeekShort selections to WeekShort?

Is this the field you are making selections in? And are you using one of these fields as dimension in your chart?

A small sample might help to understand your issue.

Re: Dynamic Labels for bar chart

apoorvasd — Mon, 23 Oct 2017 08:04:37 GMT

Hi Stefan,

Yes, WeekShort is the field in which selections can be made which has date as its values. Based on the date that a user selects in this field it should check if the date belongs to current or past months, that's the reason i have provided that condition.

I have attached sample files.

Thank you.

Re: Dynamic Labels for bar chart

swuehl — Mon, 23 Oct 2017 08:22:13 GMT

Given your sample QVW, what do you expect to see when you do some selections in WeekShort (for example, when selecting Oct in POPER_NEW, what should I select in WeekShort? And what do you expect to see in your chart?)?

Looking at this part or your label expression

GetFieldSelections(WeekShort)>=month(Date#(WeekShort,'MMM'))

Why are you using the month() function on your WeekShort date (and why are you using Date#() interpretation here, and in addition why with an incompatible format code?)?

I don't really understand what you are trying to do here.

Re: Dynamic Labels for bar chart

apoorvasd — Mon, 23 Oct 2017 10:18:37 GMT

GetFieldSelections(WeekShort)>=month(Date#(WeekShort,'MMM'))

In this expression, I am just trying to check if the month in the selected WeekShort is greater than or equal to current Month.

So when there is a selection made in WeekShort, it should check which month it belongs to and based on that the label of the bar graph should change. Ignore the expression for the bars, i want a solution for labels. I think I have explained what I want in my original post. Hope you can help me!

Thanks.

Re: Dynamic Labels for bar chart

swuehl — Mon, 23 Oct 2017 10:30:56 GMT

I've understood you are looking at the label expression.

But where in

GetFieldSelections(WeekShort)>=month(Date#(WeekShort,'MMM'))

are you checking against current month?

Re: Dynamic Labels for bar chart

apoorvasd — Mon, 23 Oct 2017 10:33:05 GMT

Current and future months

Re: Dynamic Labels for bar chart

swuehl — Mon, 23 Oct 2017 10:44:26 GMT

Seems like we have an ongoing communication problem, sorry for that. I am out of this discussion.

Just some general hints:

- WeekShort is already a date value. No need to interpret it using Date#().

- Month(WeekShort) is returning the unique month of the unique date value as a single dual value (text and number)

- If you select more than one WeekShort value from different months, Month(WeekShort) will return NULL

- GetFieldSelections() will return a comma separated string of the dates and you are comparing this to a single month value

Re: Dynamic Labels for bar chart

apoorvasd — Mon, 23 Oct 2017 10:48:02 GMT

Okay, thanks for those hints Stefan!

I will look into them.

Re: Dynamic Labels for bar chart

tresB — Mon, 23 Oct 2017 10:59:49 GMT

May be this?

=If(GetSelectedCount(WeekShort), If(Max(WeekShort)<MonthStart(Today()), 'Thereof BB','Risk BB'), 'No WeekShort is Selected')

Re: Dynamic Labels for bar chart

apoorvasd — Mon, 23 Oct 2017 11:06:14 GMT

Hi Tresesco,

This works only when i make a selection in WeekShort. I would like to apply the same condition when a month is selcted also. Month Field here is POPER_NEW.

Thank you.

Re: Dynamic Labels for bar chart

tresB — Mon, 23 Oct 2017 11:07:55 GMT

This?

=If(GetSelectedCount(WeekShort) OR GetSelectedCount(POPER_NEW), If(Max(WeekShort)<MonthStart(Today()), 'Thereof BB','Risk BB'), 'Your Suitable message')

Re: Dynamic Labels for bar chart

Peter_Cammaert — Mon, 23 Oct 2017 11:09:51 GMT

Or maybe a bit simpeler (assuming that whether you do or don't actively select months or weeks, the chart will display something, and the title should be adjusted accordingly)

=IF (Max(WeekShort) < MonthStart(Today()), 'ThereofBB', 'Risk BB')

Re: Dynamic Labels for bar chart

apoorvasd — Mon, 23 Oct 2017 11:13:24 GMT

Works Perfectly!

Thank you

Re: Dynamic Labels for bar chart

apoorvasd — Mon, 23 Oct 2017 11:14:35 GMT

Hi Peter,

Even your solution works perfectly!

Thank you very much! Not sure which one to mark as right answer now

Re: Dynamic Labels for bar chart

Peter_Cammaert — Mon, 23 Oct 2017 12:04:21 GMT

Chose the one that best answered your question. Don't worry, you won't offend me by rewarding one of my own heroes . But please do close the discussion by assigning a Correct Answer.

Since a lot of people provided valuable information, you may consider assigning Helpfuls to the replies that were most... helpful (see the Actions menu underneath each post)