Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Pivot Chart measures & sub measures

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!

3 Replies
Highlighted
Specialist
Specialist

Re: Pivot Chart measures & sub measures

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())

 

 

Highlighted
Contributor
Contributor

Re: Pivot Chart measures & sub measures

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

Highlighted
Contributor
Contributor

Re: Pivot Chart measures & sub measures

Hi,

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

Thanks