topic Re: Up/Down arrow based on pivot table which is using Valuelist in QlikView

Up/Down arrow based on pivot table which is using Valuelist

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

I have pivot table something as below. I want to show UP/Down arrow next to each of the measures. So the condition is if measure >=0 then UP Arrow(In Green) and if the measure is less <0 then DOWN arrow(In Red).

Since I have been using Valuelist here I am not sure how to achieve this here.

Please help me out to achieve this.

Re: Up/Down arrow based on pivot table which is using Valuelist

jensmunnichs — Fri, 12 Jul 2019 15:21:50 GMT

Never tried this before but according to this thread you can change the number format to ▲ #,##0;▼ #,##0 (slightly different for you as they are percentages), then use the 'visual cues' tab in the chart's properties to colour the values.

Re: Up/Down arrow based on pivot table which is using Valuelist

qlikviewforum — Fri, 12 Jul 2019 16:49:52 GMT

Thanks! Yes that i already got it. But my request was how can i use the visual cues in the valuelist.

Re: Up/Down arrow based on pivot table which is using Valuelist

qlikviewforum — Sat, 13 Jul 2019 08:50:49 GMT

Any further updates from anyone on this? Please help...

Re: Up/Down arrow based on pivot table which is using Valuelist

qlikviewforum — Mon, 15 Jul 2019 06:36:50 GMT

???

Re: Up/Down arrow based on pivot table which is using Valuelist

jensmunnichs — Mon, 15 Jul 2019 08:41:54 GMT

Hi,

I'm not entirely sure what the problem is, I created a chart using valuelist as a calculated dimension and visual cues seem to work just fine (see images below). Could you post the dimensions/expressions you are using (or even better, a copy of your application)? That would make it a lot easier to find the cause of your problems.

Re: Up/Down arrow based on pivot table which is using Valuelist

qlikviewforum — Mon, 15 Jul 2019 08:58:13 GMT

Thanks again for your response...

Application has already been attached in the initial post. Please check above and let me know if you find something ASAP...

Also please note that the up/down arrow should be in a separate column next to each of the measures. Please let me know if you have any questions.

Re: Up/Down arrow based on pivot table which is using Valuelist

qlikviewforum — Mon, 15 Jul 2019 09:17:06 GMT

Did you get any chance to look into it? Please share if you find some solution... Please ping once you have received this message...

Re: Up/Down arrow based on pivot table which is using Valuelist

jensmunnichs — Mon, 15 Jul 2019 09:54:59 GMT

Hi,

Sorry, I did not see that you had already attached your application.

Also, please have some patience, I get that you probably have deadlines to meet but I do have other things to do as well.

I've attached a qvw file to show how I would personally solve this. I simply added expressions to check whether the existing expressions were positive or negative, and then return an up or down arrow. I did swap the columns/rows for this because I think that's more clear, but that's for you to decide.

If you wanted to do it exactly as you described (your current table, but with extra columns showing increase or decrease), you'd have to add those fields to your valuelist (something like Valuelist('Last 3 months', 'Change1', 'Last 6 months', 'Change2', 'Next 3 months', Change3')) and then modify your expressions to something along the lines of:

If(Valuelist='Last 3 months', *last 3 months expression*, If(Valuelist= 'Change1', If(*last 3 months expression* < 0, down arrow, up arrow), if(Valuelist='Last 6 months', *last 6 months expression*)....... and so on

I 1000% prefer the first solution, as the second would make your expressions incredibly long, making them harder to read and they will take longer to calculate (which may or may not be a problem depending on the size of your dataset).

Hope this helps.

Re: Up/Down arrow based on pivot table which is using Valuelist

jensmunnichs — Mon, 15 Jul 2019 10:13:16 GMT

One small adjustment to what I originally posted: I changed the expressions to IF(Column(1)<0, Dual(chr(9660), -1), Dual(chr(9650), 1)), so that the arrows have a numerical value as well. This means that we can use the visual cues tab to change the colour of the arrows to green or red to match the expression results.

Re: Up/Down arrow based on pivot table which is using Valuelist

qlikviewforum — Mon, 15 Jul 2019 10:17:37 GMT

Thanks for your suggestion. Sorry to trouble you... 🙂

I have to try method 1 only though it is longer since they chart should be in the same way I have displayed.

One problem in method 1 would be I cannot control the color for Change* only. That will be a problem.

Re: Up/Down arrow based on pivot table which is using Valuelist

jensmunnichs — Mon, 15 Jul 2019 10:48:02 GMT

It's possible, but again not a great solution. If you click the '+' icon next to the expressions, then click text colour, you can set an expression to determine the colour of the expression result. You could use this to change the colour where valuelist=Change* with another long IF statement. You wouldn't have to include the if(valuelist='Last 3 months', etc. anymore but you'd still have to separate the 3 different Change columns.

Something like:

If(Valuelist('Last 3 months', 'Change1', etc.) = 'Change1', if(*Last3monthsexpression*< 0, red, green), if(Valuelist(...) = 'Change2', if(*Last6monthsexpression* < 0, red, green), if(Valuelist(...) = 'Change3', If(*Next3monthsexpression* < 0, red, green))

Again, it's not pretty, but I think it works.

Re: Up/Down arrow based on pivot table which is using Valuelist

qlikviewforum — Mon, 15 Jul 2019 10:58:29 GMT

Thanks...

Yeah it is getting bigger and bigger 🙄 not sure whether they will accept to have extra column with the column name then only I can think of it... Let check it out...

Re: Up/Down arrow based on pivot table which is using Valuelist

HirisH_V7 — Mon, 15 Jul 2019 11:26:49 GMT

Was this okay check..

HTH,

PFA

Hirish

Re: Up/Down arrow based on pivot table which is using Valuelist

qlikviewforum — Mon, 15 Jul 2019 13:00:36 GMT

It is very simple and perfect but is there is anyway to handle this if the value is null.

Re: Up/Down arrow based on pivot table which is using Valuelist

HirisH_V7 — Mon, 15 Jul 2019 16:44:36 GMT

what to be displayed in place of null.

Re: Up/Down arrow based on pivot table which is using Valuelist

qlikviewforum — Mon, 15 Jul 2019 16:59:23 GMT

Any neutral visual cues..

With this table what if the user wants to have a separate columns like change 1, change 2 and change 3 for all the 3 measures but the column names should be hidden. How that is possible... Please help me for both...

Re: Up/Down arrow based on pivot table which is using Valuelist

HirisH_V7 — Mon, 15 Jul 2019 17:22:48 GMT

you can put '0' or 'no Value' in place of null from Presentation Tab.

I don't think we can create new columns, as your'e using value list & it will become messy. Better, my suggestion is to create a table in script and Calculate all expressions there itself. Then it will be easy for you to achieve what ever required. Even in straight table also you can plot report and hide columns or other sort functionalities will become handy there.

Re: Up/Down arrow based on pivot table which is using Valuelist

qlikviewforum — Tue, 16 Jul 2019 09:30:59 GMT

As I expected the need is to have numbers in different color and visual cue in different color... So I believe I should add 3 more columns for 3 measure then only I think I will be able to achieve this... But I feel even if we do this we cannot have different colors.

Please let me know if you have any simple alternative.

Re: Up/Down arrow based on pivot table which is using Valuelist

qlikviewforum — Wed, 17 Jul 2019 10:14:45 GMT

Can you please implement this in the same structure that I had given...