Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
maprinci
Contributor II
Contributor II

How to use two measures based on two different date fields in a single pivot table?

I want to use two measures based on two different date fields in a single pivot table.  There are several key fields in my data, but below is a simplified example of the data in my load script:

Table 1:

User Id

SalesMonth

Amount (amount sold in dollars)

Table 2:

User Id

QuotaMonth

Quota (sales goal in dollars)

The tables are joined on UserId, and renaming the month fields as the same field name creates a synthetic key. My goal is to have the Month going across the top of the pivot table, with Quota and Amount as measures beneath each month (so we can do a side-by-side comparison of sales quota vs. amount sold in a pivot table).  Any help is appreciated!

1 Reply
arvind_patil
Partner - Specialist III
Partner - Specialist III

HI Melissa,

you can join data based on as follows:

[User Id]&'_'&[SalesMonth] as %Key,

[User Id]&'_'&[QuotaMonth] as %Key,


join on this,


Create pivot table as below:

 


Jan
Feb
Mar
UserIDSum(Sale)Sum(Quota)Sum(Sale)Sum(Quota)Sum(Sale)Sum(Quota)
1############
2############
3############
4############

Thanks,

Arvind Patil