topic Re: Using visual cues in pivot table in QlikView

Using visual cues in pivot table

Wed, 09 Oct 2013 05:15:26 GMT

Hi,

I need to color the concepts in green which have green background for two consecutive columns (Ex: Headlamps)

and need to color the concepts in red which have red color font for two consecutive columns. (Ex: shutter speed, disk partitioning)

I have attached the pivot table, the dimensions, the expression used and the visual cues used.

Thanks in advance!

Re: Using visual cues in pivot table

Wed, 09 Oct 2013 05:29:03 GMT

IF(Previous(Concept)=Concept, 'NO', 'YES') as NewConcept

This is how a new concept is defined.

Basically I want to study/identify which concepts have sustained over time/ which concepts have died out based on the difference in the counts of those counts.

Re: Using visual cues in pivot table

saumyashah90 — Wed, 09 Oct 2013 05:39:19 GMT

What you can do is

you have +Concept in the dimension

Click on the + sign ,you will get 3-4 option under it,

Click on background and write a expression under it

if([Incremental Change in Concepts]>0,Green(),Red());

Re: Using visual cues in pivot table

Wed, 09 Oct 2013 05:48:01 GMT

Hi Saumya,

But incremental change in concept for the whole dimension will not make sense right ?

if((Sum(Count_Concepts))-(Before(Sum(Count_Concepts)))>0,Green(),Red())

Because the variable incremental change in concepts has the function BEFORE in it. Which is dependent on the columns of the pivot.

Kindly let know if I need to provide additional data.

Re: Re: Using visual cues in pivot table

Wed, 09 Oct 2013 05:50:11 GMT

Here is the qvw with restricted data.

Kindly post the answer as a screenshot or in text. As I use a personal edition which cannot open other qvws.

Re: Using visual cues in pivot table

saumyashah90 — Wed, 09 Oct 2013 05:51:07 GMT

See i can see you have different months column.

for each you have different values
1)So either you can take the latest month and show that background in concept

2)Take the average of all and den show it,you need to think over it.

Re: Using visual cues in pivot table

saumyashah90 — Wed, 09 Oct 2013 05:51:46 GMT

and about the dimension thing,Yes you need to take it as a dimension if you are using it

Re: Using visual cues in pivot table

Wed, 09 Oct 2013 05:54:59 GMT

Hi Saumya,

I understand that part but my requirement is not just the last month and also taking the average wont suffice.

I need to figure out those concepts which have incrementally grown over time. Which means identifying those concepts that have green fields across consecutive months.

And the converse is required to identify the concepts which have declined over time.

Re: Using visual cues in pivot table

Wed, 09 Oct 2013 05:57:21 GMT

Putting the same question in general QV users sense.

Identifying customers who've bought stuff in an increasing rate throughout the time frame considered.

and identifying the customers who've reduced purchase consistently over the time frame considered.

Re: Using visual cues in pivot table

saumyashah90 — Wed, 09 Oct 2013 05:58:49 GMT

Okay there is one more question for this then,

What if it is 1in Oct,-4 in Nov,2 in Dec and -3 in Jan

So how you want to show background of the concept.

GoodOption would be.

You can check previous month and latest month ( If it has grown show green or else red)

Re: Using visual cues in pivot table

Wed, 09 Oct 2013 06:05:18 GMT

That case let it be blank, since it has random pattern.

I want to specifically identify the best and worst cases.

and how do I do that ( previous month latest month thing ??)

Re: Using visual cues in pivot table

saumyashah90 — Wed, 09 Oct 2013 06:11:56 GMT

Check Value for Latest month and previous Month.

If(Latest Month(Value)<0) and PrevMonth(Value)>0,Green,red)

Depends how you want to take it

Re: Using visual cues in pivot table

Wed, 09 Oct 2013 06:15:15 GMT

what should be in value ?

Re: Using visual cues in pivot table

saumyashah90 — Wed, 09 Oct 2013 06:19:49 GMT

I guess you are taking me wrong

Latest Month is not a function it is a field name of the latest month which consist of the values

Re: Using visual cues in pivot table

Wed, 09 Oct 2013 06:23:59 GMT

I have no formal training in QV. Self learning mode.

Can you kindly type the expression based on my data and fields that I've used. Got the point but I'm not able to formulate it.

Looks easy but I'm unable to do it.

Re: Using visual cues in pivot table

saumyashah90 — Wed, 09 Oct 2013 06:35:42 GMT

okay no problem.

you need to tell me how are you getting these values or from where you are getting this

Apr1990 Jul1990 Oct1990

these columns?

Re: Using visual cues in pivot table

Wed, 09 Oct 2013 06:37:28 GMT

It is a dimension named Start_Period

Re: Using visual cues in pivot table

saumyashah90 — Wed, 09 Oct 2013 06:40:35 GMT

Okay and its format is MMM-YYYY ? or those are text ?

Re: Using visual cues in pivot table

Wed, 09 Oct 2013 06:43:27 GMT

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

CONCEPT_TEMP:

LOAD MonthName(Start_Period) as Start_Period,

Re: Using visual cues in pivot table

saumyashah90 — Wed, 09 Oct 2013 06:50:20 GMT

Okay thats nice,

Can you Load

CONCEPT_TEMP:

Load Date(Start_Period,'MMM-YYYY') as Start_Period,

and where does the below values comes from? i.e 1,2,-4,-3 etc?