Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
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
Partner

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