Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

maprinci
New Contributor

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
Valued Contributor III

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

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

Community Browser