Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sibrulotte
Creator III
Creator III

Set analysis: dates using dates from another table

Hi, 

I have two tables joined by customer ID. One table has requested amount, adn a date of request. The other has actual revenu, and a revenu date.

I do not want to create a master calendar that would mess up the data model and create circular references.

 

Is there a way to show a table with a date as dimension, sum of requested amount for that day, and sum of actual revenue for that same day.

 

Requests:

Customer IdRequest daterequest amount
12020-04-1010
22020-04-1015
12020-04-1220
32020-04-1230
42020-04-1240
52020-04-1550

 

Actuals:

Customer IDrevenu dateactual revenu
12020-04-1212
22020-04-1216
12020-04-1221
32020-04-1228
42020-04-1242
52020-04-1246

 

 

 

Resulting graph pivot table would look like this

Daterequested amountactual amount
2020-04-10250
2020-04-1290165
2020-04-15500

 

I'm thinking I might have to build a table in the script...

1 Solution

Accepted Solutions
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Try this:

Requests:
load Key,Date,sum([request amount])as [request amount]
group by Key,
		Date; 
load [request amount],[Request date]&'|'&[Customer ID] as Key,[Request date]as Date
 inline [
Customer ID	Request date	request amount
1	2020-04-10	10
2	2020-04-10	15
1	2020-04-12	20
3	2020-04-12	30
4	2020-04-12	40
5	2020-04-15	50
](delimiter is '	'); 
join
load Key,Date,sum([actual revenu])as [actual revenu]
group by Key,
		Date; 
load [actual revenu],[revenu date]&'|'&[Customer ID] as Key,[revenu date]as Date
 inline [
Customer ID	revenu date	actual revenu
1	2020-04-12	12
2	2020-04-12	16
1	2020-04-12	21
3	2020-04-12	28
4	2020-04-12	42
5	2020-04-12	46
](delimiter is '	');

exit script;

 

Front end table:
           Dimension : Date
           Measure : sum([request amount])
                             : sum([actual revenu])

MC.PNG

View solution in original post

5 Replies
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Try this:

Dimension for pivot table:

Request Date

Expressions:

sum([request amount])

sum (DISTINCT if([Request date]=[revenu date],[actual revenu]))

sibrulotte
Creator III
Creator III
Author

thx for the try.

Returns 0 for all dates though.

 

 

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

I am getting the expected output:

MC.PNG

Do check back your Dates field, whether they are in date format.

 

sibrulotte
Creator III
Creator III
Author

Check again. I failed to give you correct data to input. Your model does not actually have a join on customer ID since one of the two has small caps for ID...

 

But if you had a propoer model that was joined, it would return 103 instead of 165. That is the total revenu for customer 1,3 and 4, the only customers to have requests on april 12th.

 

 

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Try this:

Requests:
load Key,Date,sum([request amount])as [request amount]
group by Key,
		Date; 
load [request amount],[Request date]&'|'&[Customer ID] as Key,[Request date]as Date
 inline [
Customer ID	Request date	request amount
1	2020-04-10	10
2	2020-04-10	15
1	2020-04-12	20
3	2020-04-12	30
4	2020-04-12	40
5	2020-04-15	50
](delimiter is '	'); 
join
load Key,Date,sum([actual revenu])as [actual revenu]
group by Key,
		Date; 
load [actual revenu],[revenu date]&'|'&[Customer ID] as Key,[revenu date]as Date
 inline [
Customer ID	revenu date	actual revenu
1	2020-04-12	12
2	2020-04-12	16
1	2020-04-12	21
3	2020-04-12	28
4	2020-04-12	42
5	2020-04-12	46
](delimiter is '	');

exit script;

 

Front end table:
           Dimension : Date
           Measure : sum([request amount])
                             : sum([actual revenu])

MC.PNG