Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
subbareddykm
Creator II
Creator II

Caluculate Dim

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:

 

ProjectSub ProjDateValVal 2
Project APhase-A01/06/201600
08/06/201600
15/06/201656
22/06/20161214
29/06/20161831
06/07/20161931
13/07/20161931
20/07/20162231
27/07/20162631
03/08/20163139
10/08/20163142
Phase-B29/06/20163142
27/07/20163142
10/08/20163142
17/08/20164042
Phase-C31/05/20174042
07/06/20174042
1 Solution

Accepted Solutions
tresesco
MVP
MVP

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.

View solution in original post

15 Replies
varshavig12
Specialist
Specialist

Can you please provide the expected output ?

subbareddykm
Creator II
Creator II
Author

ProjectSub ProjDateValVal 2
Project APhase-A01/06/201600
08/06/201600
15/06/201656
22/06/20161214
29/06/20161831
06/07/20161931
13/07/20161931
20/07/20162231
27/07/20162631
03/08/20163139
10/08/20163142
Phase-B17/08/20164042
Phase-C31/05/20174042
07/06/20174042
soloeeeoff
Contributor III
Contributor III

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.

tresesco
MVP
MVP

Like this may be?

Capture.PNG

subbareddykm
Creator II
Creator II
Author

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

 

ProjectSub ProjDateValVal 2
Project APhase-A01/06/201600
08/06/201600
15/06/201656
22/06/20161214
29/06/20161831
06/07/20161931
13/07/20161931
20/07/20162231
27/07/20162631
03/08/20163139
10/08/20163142
Phase-B17/08/20163142
24/08/20164042
Phase-C31/05/20174042
07/06/20174042

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.

subbareddykm
Creator II
Creator II
Author

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

Thankx in Adv

sunny_talwar

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?

soloeeeoff
Contributor III
Contributor III

Sunny,

You are right I think.

I am also thinking the same.

Thanks

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)