Pivot Chart measures & sub measures

[Seb_Sanders]

Hi,

I'm replicating an excel table in Qlik Sense and I'm struggling with replicating what are very simple steps in Excel .

I've used a Pivot Chart in Qlik, I have several columns of data that then use the year to split the data so I can compare last year to this year based on several row categories.

It's the Year on Year change I'm struggling with. For Quotes, which is a simple field in the data, I've taken this approach:

If ([Trnx Year] = year(AddMonths(Today(),-1)),
(Sum([Quote Tails]) / (Sum(TOTAL <[Response Type],SPECIES> [Quote Tails]) - SUM([Quote Tails])))-1,
Null())

Response Type and Species are rows, Quote Tails is a column, Trnx Year is a column that splits quote tails

It doesn't seem to be the best way of getting to what I want, but how do I reference another column that is being split by the year?

I've tried to read the community and the Qlik guides, but the terminology and syntax is not clear to me.

In addition, the next step I need to take is to almost filter while doing the above... so I need to group by Response Type and Species, but only where the product type is (for example) "LC"

Surely there's a simpler way to divide one column by another?

Thanks in advance!

[jwjackso]

You can use the Column  and ColumnNo  functions to reference the columns.  If  SUM([Quote Tails]) was in column 1 and Sum(TOTAL <[Response Type],SPECIES> [Quote Tails]) was in column 2, your formula becomes

If ([Trnx Year] = year(AddMonths(Today(),-1)),
(Column(1) / (Column(2) - Column(1)))-1,
Null())

[Seb_Sanders]

Hi,

That may come in really useful, however it only returns the column within the dimension... so for 2019 it returns the 2nd column (2019 quotes) but for 2020 it returns the 2nd column within 2020, which is (2020 quotes).

How can I access the 2019 quotes column within 2020?

Thanks

[Seb_Sanders]

Hi,

Thanks for your help, I've resolved this using the "Before" function which has simplified the query massively.

Thanks