Attempting to make a Chart more robust/pivot data

Fri, 12 Dec 2014 19:58:50 GMT

Hello all,

I have an idea that I would like to explore, but can't really nail down how to approach my script for this addition I would like to make.

To explain I have a bar chart that I created to help identify on today's date what project milestones were acheived yesterday, and what projects milestones were missed. I would like to create button(s) (maybe a slider) that will let me change what today is. I believe it is imporant to note that yesterdays date is based on the 'Milestone.Revised Date' field.

Within this chart I have a several expressions.

Achievement:

=count(
{<
[Project Details.Load Date]={'*'}
, [TD.DateWeekEnd]={"$(=WeekEnd([Project Details.Load Date]))"}
>}
if( [Project.Load Date]=[Project Details.Load Date]
AND NOT [Project Status]='PROJECT CANCELLED' AND NOT [Project Status]='ON HOLD' AND NOT [Project Status]='PROJECT AWARDED'
AND Milestone.Flag = 0
AND NOT IsNull([Milestone.Actual Date])
AND [Milestone.Revised Date]=[Project Details.Load Date]
, [Milestone]))

Missed:

count(
{<
[Project Details.Load Date]={'*'}
, [TD.DateWeekEnd]={"$(=WeekEnd([Project Details.Load Date]))"}
>}
if( [Milestone.Load Date]=[Project Details.Load Date]
AND [Milestone.Revised Date]=[Project Details.Load Date]
AND isNull([Milestone.Actual Date])
AND NOT [Project Status]='PROJECT CANCELLED' AND NOT [Project Status]='ON HOLD' AND NOT [Project Status]='PROJECT AWARDED' AND NOT [Project Status]='COMPLETED'
AND Milestone.Flag = 0
, [Project Number]))

I then have another query that was develped by a colleague in which runs on a count if based query to identify x out of y:

='( '&

count(
{<
[Project Details.Load Date]={'*'}
, [TD.DateWeekEnd]={"$(=WeekEnd([Project Details.Load Date]))"}
>}
if( [Milestone.Load Date]=[Project Details.Load Date]
AND NOT [Project Status]='PROJECT CANCELLED' AND NOT [Project Status]='ON HOLD' AND NOT [Project Status]='PROJECT AWARDED'
AND Milestone.Flag = 0
AND NOT IsNull([Milestone.Actual Date])
AND [Milestone.Revised Date]=[Project Details.Load Date]

, [Milestone]))
&' out of '&
(count(
{<
[Project Details.Load Date]={'*'}
, [TD.DateWeekEnd]={"$(=WeekEnd([Project Details.Load Date]))"}
>}
if( [Milestone.Load Date]=[Project Details.Load Date]
AND NOT [Project Status]='PROJECT CANCELLED' AND NOT [Project Status]='ON HOLD' AND NOT [Project Status]='PROJECT AWARDED'
AND Milestone.Flag = 0
AND NOT IsNull([Milestone.Actual Date])
AND [Milestone.Actual Date]<=[Milestone.Revised Date]
AND [Milestone.Revised Date]=[Project Details.Load Date]
AND WeekDay([Milestone.Revised Date]=TD.DateWeekday)

, [Milestone]))
+
count(
{<
[Project Details.Load Date]={'*'}
, [TD.DateWeekEnd]={"$(=WeekEnd([Project Details.Load Date]))"}
>}
if( [Milestone.Load Date]=[Project Details.Load Date]
AND [Milestone.Revised Date]=[Project Details.Load Date]
AND isNull([Milestone.Actual Date])
AND NOT [Project Status]='PROJECT CANCELLED' AND NOT [Project Status]='ON HOLD' AND NOT [Project Status]='PROJECT AWARDED' AND NOT [Project Status]='COMPLETED'
AND Milestone.Flag = 0
, [Milestone])))
&' )'

As always any help is greatly appreciated.

Regards,

Myles

Re: Attempting to make a Chart more robust/pivot data

marcus_sommer — Fri, 12 Dec 2014 23:11:55 GMT

I think your approach could work but is very complex and it would be quite difficult to maintain and extend. I suggest you should go back one or two steps and look if you could optimize your datamodel and make some associations and calculations in the script. Maybe these links could give you some hints:

Recipe for a Gantt chart

Files for creation of a Gantt chart

- Marcus

Re: Attempting to make a Chart more robust/pivot data

Mon, 15 Dec 2014 13:22:08 GMT

Well it's nice to know that I'm not just me being green with regards to Qlikview skills.

Thanks, Marcus I'll review the material you have included in your reply.

Thank you.

topic Attempting to make a Chart more robust/pivot data in QlikView

Attempting to make a Chart more robust/pivot data

Re: Attempting to make a Chart more robust/pivot data

Re: Attempting to make a Chart more robust/pivot data