How we can display field row values as column headings

[#1]

Hello Every one,

I was trying to display field values as column headings( horizontal format) and that column field value as with "Trend" hyperlink.

If I click Trend hyperlink it should be display the line chart .

please see the attached images for your understand and suggest me how we can achieve it.

Thanks

Swathi

[#2]

Hi Swati,

You have not attached any images.

Thanks

Sabal

[#3]

here it is

[JonnyPoole]

I changed the expressions to text from link and then just formatted them as blue/underline to look like links.

When selected, getselectedcount(Metric) evaluates to 1. And with that you can control which metric shows in a detailed trend chart.

take a look at the example.  you'll need to add conditional expressions depending on which metric is selected. also the trend chart is hidden unless there is only 1 selected metric.

help ?

[#5]

Thanku Jonathan for your quick response,but I would like to display text "Trend" instead of digits.looks like below format

Output:

No Of recepts	Avg std Leadtime & AWLT(Combination of 2 fields here)	OTD%,Short Cycle & Request Met(Combination of 3 fields here)	P95Days(Late) & P5Days(Early)
Trend	Trend	Trend	Trend

Now if I click on Trend I would like to display the SUM of values in line chart.So is it possible to do it in qlikview.

Thanks

Swathi

[JonnyPoole]

you can actually just type 'Trend' in the expression definition to show Trends. Attached is the updated example.

in the trend chart you can use SUM or any other expression to show the results you want.

But i couldn't build a trend chart according to your screenshot because  I didn't find any data fields in your QVW to show a weekly numbers.

So for your 2nd question i'm not 100% sure what you want to show in the trend.  

[#7]

Thanku so much Jonathan ...looks we are almost near to our expected output for my 1st question.

but have some differences between expected output and your qvw output.

see the image for QVW output.

and

Expected output is combination of two or 3 fields as shown as previous mail.

So is it possible to merge two or three fields in single column ??

Thanks

Swathi

[JonnyPoole]

For the 1st question i created a new 'Metric Category' field that buckets 1 or more of the buckets together based on your screenshot. I did this in the load script. The Data: table below represents the table your qlikview script produces today. Below that i have a new metric category table that loads in all the metrics and depending on the name creates a new 'metric category' tag.

Then i use the metric category in the dimension so you get the labels you want.

In the trend you can show 1 or more expressions in a line or combo chart depending on the metric category you select.

Data:

LOAD CRETD_BY,

    CRETD_DT,

    LAST_UPDTD_BY,

    LAST_UPDTD_DT,

    Metric,

    MSR_ID,

    PLANT_NAM,

    POLE_NAM,

    PRIOR_FW_VAL,

    PRIOR_MONTH_VAL,

    PRIOR_QTR_VAL,

    PRIOR_YR_VAL,

    SUPPLIER_FLG

FROM

(biff, embedded labels, table is Sheet1$);

MetricCategory:

load

  if(Metric='NO OF RECEIPTS','NO OF RECEIPTS',

  if(Metric='AVG. STD. LEAD TIME' or Metric='$ AWLT','AVG. STD. LEAD TIME  &  $ AWLT',

  if(Metric='ON TIME DELIVERY %' or Metric='REQUEST MET %' or Metric='SHORT CYCLE %', 'ON TIME DELIVERY %  &  REQUEST MET %  &  SHORT CYCLE %',

  if(Metric='P5 DAYS (EARLY)' or Metric='P95 DAYS (LATE)','P5 DAYS (EARLY)  &  P95 DAYS (LATE)'

  )

  )

  )

  ) as MetricCategory,

  Metric

resident Data;

exit script;

[#9]

great thanku Jonathan ,looks good but is it possible to remove Metric Category column alone,I dont want to show it I want to hide it.

Let me work on my 2nd question,I believe for my 2nd question I need to add master calender table to my datamodel.

Thanks

Swathi

[JonnyPoole]

You can prevent the metric category from showing up in the current selections box. Would that help ?

To do that you need to set a hidden field prefix in the load script like this:

set HidePrefix='_' ;

And then when you load the MetricCategory , prefix it with an '_' like this:

MetricCategory:

load

  if(Metric='NO OF RECEIPTS','NO OF RECEIPTS',

  if(Metric='AVG. STD. LEAD TIME' or Metric='$ AWLT','AVG. STD. LEAD TIME  &  $ AWLT',

  if(Metric='ON TIME DELIVERY %' or Metric='REQUEST MET %' or Metric='SHORT CYCLE %', 'ON TIME DELIVERY %  &  REQUEST MET %  &  SHORT CYCLE %',

  if(Metric='P5 DAYS (EARLY)' or Metric='P95 DAYS (LATE)','P5 DAYS (EARLY)  &  P95 DAYS (LATE)'

  )

  )

  )

  ) as _MetricCategory,

  Metric

resident Data;