topic Re: Caluculate Dim in QlikView

Caluculate Dim

subbareddykm — Wed, 25 Nov 2020 16:16:04 GMT

Hi All ,

i m trying to create one caluculate dim in my chart.

i.e In my data i have a Project and phases and respective dates.

What i wan is , if i m in Phase -B i want to show only greater then dates only.

For ref Data:

Project	Sub Proj	Date	Val	Val 2
Project A	Phase-A	01/06/2016	0	0
		08/06/2016	0	0
		15/06/2016	5	6
		22/06/2016	12	14
		29/06/2016	18	31
		06/07/2016	19	31
		13/07/2016	19	31
		20/07/2016	22	31
		27/07/2016	26	31
		03/08/2016	31	39
		10/08/2016	31	42
	Phase-B	29/06/2016	31	42
		27/07/2016	31	42
		10/08/2016	31	42
		17/08/2016	40	42
	Phase-C	31/05/2017	40	42
	Phase-C	07/06/2017	40	42

Re: Caluculate Dim

varshavig12 — Mon, 26 Sep 2016 05:48:46 GMT

Can you please provide the expected output ?

Re: Caluculate Dim

subbareddykm — Mon, 26 Sep 2016 05:58:56 GMT

Project	Sub Proj	Date	Val	Val 2
Project A	Phase-A	01/06/2016	0	0
		08/06/2016	0	0
		15/06/2016	5	6
		22/06/2016	12	14
		29/06/2016	18	31
		06/07/2016	19	31
		13/07/2016	19	31
		20/07/2016	22	31
		27/07/2016	26	31
		03/08/2016	31	39
		10/08/2016	31	42
	Phase-B	17/08/2016	40	42
	Phase-C	31/05/2017	40	42
	Phase-C	07/06/2017	40	42

Re: Caluculate Dim

soloeeeoff — Mon, 26 Sep 2016 06:35:52 GMT

Hi Subbareddy,

Can you please give more details about (Dimension, Calculated Dimension, Expressions used upto what you have finished ) & Input data ( as in the source ) & Output view expected .

Thanks.

Re: Caluculate Dim

tresB — Mon, 26 Sep 2016 06:38:55 GMT

Like this may be?

Re: Caluculate Dim

subbareddykm — Mon, 26 Sep 2016 07:10:55 GMT

In this It is ok But, What about Below case:

Project	Sub Proj	Date	Val	Val 2
Project A	Phase-A	01/06/2016	0	0
		08/06/2016	0	0
		15/06/2016	5	6
		22/06/2016	12	14
		29/06/2016	18	31
		06/07/2016	19	31
		13/07/2016	19	31
		20/07/2016	22	31
		27/07/2016	26	31
		03/08/2016	31	39
		10/08/2016	31	42
	Phase-B	17/08/2016	31	42
	Phase-B	24/08/2016	40	42
	Phase-C	31/05/2017	40	42
	Phase-C	07/06/2017	40	42

It will pick only one max date right.

What exact i m saying is , my dates should be gretter then of Last Sub_Proj for all.

Re: Caluculate Dim

subbareddykm — Mon, 26 Sep 2016 07:20:55 GMT

If it is difficult get it in front end and back is also ok to me ...

Thankx in Adv

Re: Caluculate Dim

sunny_talwar — Mon, 26 Sep 2016 07:37:36 GMT

Not sure about others, but I still don't understand the logic behind choosing what dates you want to show for Phase-B. Can you elaborate or explain with an example?

Re: Caluculate Dim

soloeeeoff — Mon, 26 Sep 2016 07:39:11 GMT

Sunny,

You are right I think.

I am also thinking the same.

Thanks

Re: Caluculate Dim

Anil_Babu_Samineni — Mon, 26 Sep 2016 07:39:56 GMT

From This Data, What is the expected o/p. Can you elaborate more may be

Re: Caluculate Dim

subbareddykm — Mon, 26 Sep 2016 08:32:37 GMT

Input Data:

Project	Sub Proj	Date	Val	Val 2
Project A	Phase-A	01/06/2016	0	0
		08/06/2016	0	0
		15/06/2016	5	6
		22/06/2016	12	14
		29/06/2016	18	31
		06/07/2016	19	31
		13/07/2016	19	31
		20/07/2016	22	31
		27/07/2016	26	31
		03/08/2016	31	39
		10/08/2016	31	42
	Phase-B	15/06/2016	5	6
		22/06/2016	12	14
		17/08/2016	31	42
		24/08/2016
		02/09/2016	40	42
	Phase-C	31/05/2017	40	42
	Phase-C	07/06/2017	40	42

Output Is :

Project	Sub Proj	Date	Val	Val 2
Project A	Phase-A	01/06/2016	0	0
		08/06/2016	0	0
		15/06/2016	5	6
		22/06/2016	12	14
		29/06/2016	18	31
		06/07/2016	19	31
		13/07/2016	19	31
		20/07/2016	22	31
		27/07/2016	26	31
		03/08/2016	31	39
		10/08/2016	31	42
	Phase-B	17/08/2016	31	42
		24/08/2016	31	42
		02/09/2016	40	42
	Phase-C	31/05/2017	40	42
	Phase-C	07/06/2017	40	42

I hope u understood.

Re: Caluculate Dim

sunny_talwar — Mon, 26 Sep 2016 08:40:18 GMT

How do you decide to pick everything after 17/08/2016? What is so special about that date?

Re: Caluculate Dim

varshavig12 — Mon, 26 Sep 2016 08:47:54 GMT

What little I understood is,

Your Phase A ends at 10th aug, 2016,

so in your chart, you want phase B to start after 10th aug date i.e 17/08/2016 as per the above sample data. Right ?

Re: Caluculate Dim

subbareddykm — Mon, 26 Sep 2016 08:48:19 GMT

If u check my last reply , i mentioned there , For Every Sub_proj the Date should be greater than of last Sub_proj of Max date...

Re: Caluculate Dim

subbareddykm — Mon, 26 Sep 2016 08:49:06 GMT

Yes exactly

Re: Caluculate Dim

tresB — Mon, 26 Sep 2016 08:51:09 GMT

Try like:

Table:

Load

Autonumber(Project&"Sub Proj") as Key,

Project,"Sub Proj",

Date(Date) as Date,

Val,

"Val 2"

Inline [

Project,Sub Proj,Date,Val,Val 2

Project A,Phase-A,42522,0,0

Project A,Phase-A,42529,0,0

Project A,Phase-A,42536,5,6

Project A,Phase-A,42543,12,14

Project A,Phase-A,42550,18,31

Project A,Phase-A,42557,19,31

Project A,Phase-A,42564,19,31

Project A,Phase-A,42571,22,31

Project A,Phase-A,42578,26,31

Project A,Phase-A,42585,31,39

Project A,Phase-A,42592,31,42

Project A,Phase-B,42550,31,42

Project A,Phase-B,42578,31,42

Project A,Phase-B,42592,31,42

Project A,Phase-B,42599,40,42

Project A,Phase-B,42810,40,42

Project A,Phase-B,42920,40,42

Project A,Phase-C,42886,40,42

Project A,Phase-C,42893,40,42

];

Left Join

load

Peek(MaxDate) as PreMaxDate;

Load

Autonumber(Project&"Sub Proj") as Key,

max(Date) as MaxDate

Resident Table Group By Project,"Sub Proj";

Final:

Load

Project,

"Sub Proj",

Date,

Val,

"Val 2"

Resident Table Where len(trim(PreMaxDate))=0 or Date>PreMaxDate;

DROP Table Table;

Note: This actually loads all dates (for the subsequent Sub Proj) when it's Date is greater than earlier Sub Proj's maximum date. So with this logic data load order matters. Hence you might have to use ORDER BY clause additionally in real scenario.