topic Multiple Metrics in One Cross Tab in QlikView

Multiple Metrics in One Cross Tab

Thu, 06 Jan 2011 20:55:25 GMT

All,

I want to thank you in advance for taking a look at this issue. Essentially, I need to create a cross tab table that has products along the x-axis on the top and a list of metrics serving as the y-axis. it should look something like this:

	Product A	Product B	Product C	Product D	Product E
Last Week Volume	100	200	350	400	140
4 Week Avg	125	180	275	375	120
Prior Year 4 Week Average	115	160	250	350	100
YTD Volume	2500	3700	3975	3875	920

I have successfully added the volume for the previous week. When I add the 4 week volume average, all of the numbers per product are the same, so I get something that looks like this:

	Product A	Product B	Product C	Product D	Product E
Last Week Volume	125	180	275	375	120
4 Week Avg	125	180	275	375	120
Prior Year 4 Week Average	125	180	275	375	120
YTD Volume	125	180	275	375	120

Any help you could provide would be greatly appreciated

Multiple Metrics in One Cross Tab

pover — Thu, 06 Jan 2011 21:39:09 GMT

Please give us a little more information.

Are you constructing this with a pivot table or a horizontal straight table?

What are the dimensions?

What are the expressions?

Regards.

Multiple Metrics in One Cross Tab

Oleg_Troyansky — Thu, 06 Jan 2011 21:40:24 GMT

The biggest question is - how do you calculate those averages? The problem is hiding in your expression formulas. Please show your expressions here or post a small example.

Multiple Metrics in One Cross Tab

Oleg_Troyansky — Thu, 06 Jan 2011 21:41:01 GMT

Karl - LOL 🙂

Multiple Metrics in One Cross Tab

Thu, 06 Jan 2011 21:49:20 GMT

Thank you for taking the time to consider my issue. Currently I am trying to construct this using Pivot table. The dimension for the pivot table is product class. The Expressions for the table are as follows:

Last Week Volume: sum ( {$} {$<Week = {$(=Only(Week)-1)}>} [Apportioned TRx Volume] )

4 Week Volume: sum ( {$} {$<Week = {$(=Only(Week)-4)}>} [Apportioned TRx Volume] )

At present, the values for each product class are repeating per product class. I would upload the file but the products have the client names embedded.

Again, thank you for your help and I look forward to hearing from you.

Multiple Metrics in One Cross Tab

pover — Thu, 06 Jan 2011 22:10:01 GMT

Set analysis will sometimes give you a total result when something is wrong with the definition of the set. This is tricky to debug without having the data especially since dates can have varying formats, but in the first place get rid of the {$} since that is not necessary. Also, if you are selecting a year or month along with the selection of week, make sure you clear that selection in the set analysis like the following example:

sum({$<Year=,Month=,Week={$(=only(Week)-1)}>} [Apportioned TRx Volume])

Regards.

Multiple Metrics in One Cross Tab

Thu, 06 Jan 2011 22:36:43 GMT

I have a relatively small data set. Let me see if I can generalize it and post the example.