topic Re: Adding a 2nd Column Dimension in QlikView

Adding a 2nd Column Dimension

Thu, 19 Jul 2012 15:35:25 GMT

Hello:

I have a straight table that is displaying information about YTD sales, YTD units, YTD COGS, etc. My dimension is year (2012 and 2011 are displayed). Currently I have variance for each of these row items being displayed in the row below.

Year Num 2012 2011

YTD Sales

Variance

YTD Units

Variance

YTD COGS

Variance

Variance is being calculated as follows: ("YTD Sales" - Below("YTD Sales")) / Below("YTD Sales")

What I'm wondering is if there is any way to display variance as a column instead of a row.

Year Num 2012 2011 Variance

YTD Sales

YTD Units

YTD COGS

Any help with this would be greatly appreciated. Also, see the attached file for a look at the table.

Re: Adding a 2nd Column Dimension

Nicole-Smith — Fri, 20 Jul 2012 16:55:50 GMT

Can you post an example of how your data is set up? It should be possible, but the way to do it depends on your data...

Re: Adding a 2nd Column Dimension

Mon, 23 Jul 2012 16:09:18 GMT

Hi Nicole:

The expressions are layed out like this:

If(Max(Total CCYTD) = 1,

$(vCCYTDSales) & $(vPCYTDSales),

$(vMaxCYSales) & $(vMax-1CYSales)

CCYTD is a flag for the current calendar year to date. PCYTD is a flag for the previous calendar year to date.

vCCYTDSales: Sum({$<CCYTD={1}>}SalesDol)

vPCYTDSales: Sum({$<PCYTD={1}>}SalesDol)
vMaxCYSales: Sum({$<CYYearNum={$(=Max(Total CYYearNum))}>}SalesDol)

vMax-1CYSales: Sum({$<CYYearNum={$(=Max(Total CYYearNum-1))}>}SalesDol)

YTD units and COGS are calculated the same way--just sub in Units/COGS for SalesDol. The only difference was for units I had to use a + instead of the & between current and previous and max and max-1. When I tried to use & for this expression it was adding on extra 0s to the total.

Thanks,

Ben D.

Re: Adding a 2nd Column Dimension

Nicole-Smith — Mon, 23 Jul 2012 18:56:29 GMT

I'm sorry, but I am still having a hard time picturing how your data is set up. Is there any way that you can post a sample .qvw file? (You can strip out data that I don't need to see, and if names or other things need to be scrambled you can do so in Settings > Document Properties > Scrambling, but make sure not to scramble any keys!) Once I have the .qvw file, I should be able to walk you through how to set up your table.

Re: Adding a 2nd Column Dimension

Tue, 24 Jul 2012 12:14:05 GMT

Hello again and thank you for taking the time to try and help me figure out this problem. I found a solution to this in a similar thread:

If(Dimensionality(),

Money($(vCCYTDSales) & $(vPCYTDSales),'$#,##0'),

Num(($(vCCYTDSales) - $(vPCYTDSales) / $(vPCYTDSales), '#.00%'))

In the presentation tab select show partial sums and variance for the label (for CYYearNum).

From my understanding dimensionality says: if the value falls within the given dimension then place it there, else use the following for the totals column.

Thank you again for your help Nicole.

Ben D.