Year, month, week format?

[Anonymous]

Hi,

is it possible to produce the following format in QlikView?

I have tried several things without luck.  Any help appreciated

[Anonymous]

I generated a dedicated calendar:-

Load

TempDate AS %DateEmpFormAdded,

Week(TempDate) As DateEmpWeek,

Year(TempDate) As DateEmpYear,

Month(TempDate) As DateEmpMonth,

Day(TempDate) As DateEmpDay,

ApplyMap('QuartersMap', month(TempDate), Null()) as DateEmpQuarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as DateEmpWeekYear,

Date(Date#(Year(TempDate) & Month(TempDate), 'yyyyMMM'), 'MMMyyyy') as DateEmpYearMonth,

WeekDay(TempDate) as DateEmpWeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Laura Castagna

Business Systems and Process Analyst, NSW Business Chamber

140 Arthur Street North Sydney NSW 2060

Tel: 02 9458 7804 | Mob: 0429 486 934 | Web: www.nswbusinesschamber.com.au<http://www.nswbusinesschamber.com.au>

Twitter<http://www.twitter.com/nswbc> | Facebook<https://www.facebook.com/NSWBusinessChamber> | LinkedIn<http://www.linkedin.com/company/388425?trk=saber_s000001e_1000> | YouTube<http://www.youtube.com/nswbctv>

<http://www.nswbusinesschamber.com.au/>

[Anonymous]

And linked it to the ,ItemCreatedWhen as %DateEmpFormAdded

Laura Castagna

Business Systems and Process Analyst, NSW Business Chamber

140 Arthur Street North Sydney NSW 2060

Tel: 02 9458 7804 | Mob: 0429 486 934 | Web: www.nswbusinesschamber.com.au<http://www.nswbusinesschamber.com.au>

Twitter<http://www.twitter.com/nswbc> | Facebook<https://www.facebook.com/NSWBusinessChamber> | LinkedIn<http://www.linkedin.com/company/388425?trk=saber_s000001e_1000> | YouTube<http://www.youtube.com/nswbctv>

<http://www.nswbusinesschamber.com.au/>

[Anonymous]

Thanks Sunny. That’s exactly what I want. You rock ☺

One more thing please. how do I reorder the Engagement Type. Ie . if I want Diagnostics to appear first?

Laura Castagna

Business Systems and Process Analyst, NSW Business Chamber

140 Arthur Street North Sydney NSW 2060

Tel: 02 9458 7804 | Mob: 0429 486 934 | Web: www.nswbusinesschamber.com.au<http://www.nswbusinesschamber.com.au>

Twitter<http://www.twitter.com/nswbc> | Facebook<https://www.facebook.com/NSWBusinessChamber> | LinkedIn<http://www.linkedin.com/company/388425?trk=saber_s000001e_1000> | YouTube<http://www.youtube.com/nswbctv>

<http://www.nswbusinesschamber.com.au/>

[sunny_talwar]

You can use a sort expression on the sort tab:

=Match(Only({1}#1), 'Diagnostics', '2ndPositionedLabel', '3rdPositionedLabel', ...and so on...)

[Anonymous]

Thanks. Hey Sunny, the year is calculating the month rather than the year?

Laura Castagna

Business Systems and Process Analyst, NSW Business Chamber

140 Arthur Street North Sydney NSW 2060

Tel: 02 9458 7804 | Mob: 0429 486 934 | Web: www.nswbusinesschamber.com.au<http://www.nswbusinesschamber.com.au>

Twitter<http://www.twitter.com/nswbc> | Facebook<https://www.facebook.com/NSWBusinessChamber> | LinkedIn<http://www.linkedin.com/company/388425?trk=saber_s000001e_1000> | YouTube<http://www.youtube.com/nswbctv>

<http://www.nswbusinesschamber.com.au/>

[sunny_talwar]

Really? I used this expression

=Count({<%DateEmpFormAdded = {"$(='>=' & Date(AddYears(Max(%DateEmpFormAdded), - 1)) & '<=' & Date(AddYears(Max(%DateEmpFormAdded), 0)))"}>}EngagementType)

Should be calculating the Count for last one year from Max(%DateEmpFormAdded) based on selections

[Anonymous]

Check this out:-

Ive tried using both the YearMonth and the Year , Month (i.e.separately) but still only calculates the month in the year column:-

Laura Castagna

Business Systems and Process Analyst, NSW Business Chamber

140 Arthur Street North Sydney NSW 2060

Tel: 02 9458 7804 | Mob: 0429 486 934 | Web: www.nswbusinesschamber.com.au<http://www.nswbusinesschamber.com.au>

Twitter<http://www.twitter.com/nswbc> | Facebook<https://www.facebook.com/NSWBusinessChamber> | LinkedIn<http://www.linkedin.com/company/388425?trk=saber_s000001e_1000> | YouTube<http://www.youtube.com/nswbctv>

<http://www.nswbusinesschamber.com.au/>

[sunny_talwar · Accepted solution]

I guess you need to ignore selections in other date fields:

=Count({<%DateEmpFormAdded = {"$(='>=' & Date(AddYears(Max(%DateEmpFormAdded), - 1)) & '<=' & Date(AddYears(Max(%DateEmpFormAdded), 0)))"}, DateEmpMonth, DateEmpYear, DateEmpMonthYear>}EngagementType)

[Anonymous]

Thanks Sunny.  Can I select what colours to wish to dedicate a Legend to.  For example if I have a scatter graph displying Members by State, can i select yellow for NSW, Blue for Vic etc?

[sunny_talwar]

I think you can do this. Have you tried doing giving it a try?