Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
Specialist
Specialist

Can you please provide the expected output ?

Highlighted
Creator II
Creator II

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

Highlighted
MVP
MVP

Like this may be?

Capture.PNG

Highlighted
Creator II
Creator II

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.

Highlighted
Creator II
Creator II

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

Thankx in Adv

Highlighted

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?

Highlighted
Contributor III
Contributor III

Sunny,

You are right I think.

I am also thinking the same.

Thanks

Highlighted

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

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)