topic Re: Set analysis: dates using dates from another table in QlikView

Set analysis: dates using dates from another table

sibrulotte — Fri, 17 Apr 2020 00:10:29 GMT

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

Actuals:

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

Resulting graph pivot table would look like this

Date	requested amount	actual amount
2020-04-10	25	0
2020-04-12	90	165
2020-04-15	50	0

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

Re: Set analysis: dates using dates from another table

Arthur_Fong — Fri, 17 Apr 2020 02:02:38 GMT

Try this:

Dimension for pivot table:

Request Date

Expressions:

sum([request amount])

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

Re: Set analysis: dates using dates from another table

sibrulotte — Fri, 17 Apr 2020 02:27:15 GMT

thx for the try.

Returns 0 for all dates though.

Re: Set analysis: dates using dates from another table

Arthur_Fong — Fri, 17 Apr 2020 02:38:29 GMT

I am getting the expected output:

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

Re: Set analysis: dates using dates from another table

sibrulotte — Fri, 17 Apr 2020 12:53:38 GMT

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.

Re: Set analysis: dates using dates from another table

Arthur_Fong — Sat, 18 Apr 2020 07:24:52 GMT

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