topic Showing revenue depending on 2 different date-fields in a bar chart in QlikView

Showing revenue depending on 2 different date-fields in a bar chart

thanhphongle — Mon, 26 Mar 2018 09:15:29 GMT

Hello community

This is my table

order_id	order_date	delivery_date	revenue
1001	20.01.2018	21.01.2018	100
1002	20.01.2018	21.01.2018	100
1003	27.01.2018	29.01.2018	200
1004	27.01.2018	29.01.2018	200
1005	31.01.2018	01.02.2018	300
1006	31.01.2018	01.02.2018	300
1007	25.02.2018	27.02.2018	100
1008	26.02.2018	28.02.2018	100
1009	28.02.2018	01.03.2018	200
1010	15.03.2018	15.03.2018	500
1011	30.03.2018	31.03.2018	200
1012	31.03.2018	01.04.2018	100

Target:

Showing revenue per month with delivery_date considering the order_date in a bar chart

Dimension:

order_date (Main dimension)

delivery_date

KPI:

sum(revenue)

How it should be calculated:

	Jan	Fev	Mar
Revenue by order_date	1200	400	800
revenue by delivery_date	600	800	900

How it should look like at the end (ignoring avril). The main dimension is delivery_date:

Hope anyone can really understand what I'm trying to say.

Re: Showing revenue depending on 2 different date-fields in a bar chart

YoussefBelloum — Mon, 26 Mar 2018 09:55:54 GMT

Hi,

here is one way to do it:

PFA

Re: Showing revenue depending on 2 different date-fields in a bar chart

thanhphongle — Mon, 26 Mar 2018 10:13:46 GMT

Hello youssef,

thanks for your response.

I actually don't understand for what die column "Flag" is for and how you managed to visualize the bar chart. Did you use the set analysis function?

Re: Showing revenue depending on 2 different date-fields in a bar chart

thanhphongle — Mon, 26 Mar 2018 11:46:15 GMT

Oh I didn't see the attached file. Is there no other solution besides creating an independant date?

Re: Showing revenue depending on 2 different date-fields in a bar chart

YoussefBelloum — Mon, 26 Mar 2018 12:16:21 GMT

I'm sure there is other solutions for this..

Maybe stalwar1‌ can give a second option here ?

Re: Showing revenue depending on 2 different date-fields in a bar chart

jonathandienst — Mon, 26 Mar 2018 12:21:28 GMT

The island date field is one way. Or you can connect the two date fields to a canonical calendar, which is very well explained here: Canonical Date

Re: Showing revenue depending on 2 different date-fields in a bar chart

sunny_talwar — Mon, 26 Mar 2018 12:23:34 GMT

I would suggest on the similar lines, but instead of duplicating all fact table rows, do this duplication in a separate table.... I called it Bridge_Table, but its a concept similar to Canonical Date‌

revenue:

LOAD * INLINE [

order_id, order_date, delivery_date, revenue

1001, 20.01.2018, 21.01.2018, 100

1002, 20.01.2018, 21.01.2018, 100

1003, 27.01.2018, 29.01.2018, 200

1004, 27.01.2018, 29.01.2018, 200

1005, 31.01.2018, 01.02.2018, 300

1006, 31.01.2018, 01.02.2018, 300

1007, 25.02.2018, 27.02.2018, 100

1008, 26.02.2018, 28.02.2018, 100

1009, 28.02.2018, 01.03.2018, 200

1010, 15.03.2018, 15.03.2018, 500

1011, 30.03.2018, 31.03.2018, 200

1012, 31.03.2018, 01.04.2018, 100

];

Bridge_Table:

LOAD order_id,

order_date as Date,

'order' as Flag

Resident revenue;

Concatenate (Bridge_Table)

LOAD order_id,

delivery_date as Date,

'delivery' as Flag

Resident revenue;

Re: Showing revenue depending on 2 different date-fields in a bar chart

YoussefBelloum — Mon, 26 Mar 2018 12:27:14 GMT

Thanks

Re: Showing revenue depending on 2 different date-fields in a bar chart

YoussefBelloum — Mon, 26 Mar 2018 12:27:59 GMT

Thanks jontydkpi

Re: Showing revenue depending on 2 different date-fields in a bar chart

YoussefBelloum — Mon, 26 Mar 2018 12:30:03 GMT

one last thing here stalwar1‌, If I don't want or can't create another Date field.. Is it possible with some dimension expression and set analysis to use these original dates (order and delivery) and get the OP's expected output ?

Re: Showing revenue depending on 2 different date-fields in a bar chart

sunny_talwar — Mon, 26 Mar 2018 12:32:22 GMT

AFAIK may be not... but even it was... it would be a horrible looking and horrible performing expression...

Re: Showing revenue depending on 2 different date-fields in a bar chart

YoussefBelloum — Mon, 26 Mar 2018 12:37:23 GMT

I see.. Good to know

Re: Showing revenue depending on 2 different date-fields in a bar chart

thanhphongle — Tue, 27 Mar 2018 07:15:27 GMT

Thank guys,

I tried with the bridge and it work very good for this data sample. Maybe another question. I have a big data here. About 1 billion datas. The current load take about 2 hours. With the concatenate load the loading process will take more time I guess no?

Re: Showing revenue depending on 2 different date-fields in a bar chart

sunny_talwar — Tue, 27 Mar 2018 08:55:03 GMT

Unfortunately it will... not sure what other option do you have...

Re: Showing revenue depending on 2 different date-fields in a bar chart

thanhphongle — Tue, 27 Mar 2018 09:32:24 GMT

Ok, thanks a lot for your help guys!