topic Re: Year, month, week format? in QlikView

Year, month, week format?

Anonymous — Wed, 27 Apr 2016 01:17:33 GMT

Hi,

is it possible to produce the following format in QlikView?

I have tried several things without luck. Any help appreciated

Re: Year, month, week format?

sunny_talwar — Wed, 27 Apr 2016 01:18:58 GMT

How exactly are you getting those numbers? May be if you can share your sample data with the logic behind the numbers, we might be able to offer some help

Re: Year, month, week format?

Anonymous — Wed, 27 Apr 2016 01:24:40 GMT

For each I have an expression Engagement Type I have separated into prospect and lead.

=if(#1='Book' and EngagementType='Lead',count(EngagementType))

Re: Year, month, week format?

sunny_talwar — Wed, 27 Apr 2016 01:26:28 GMT

I am looking for the raw data (dummy should work) in an Excel tile to create something similar for what you currently have

Re: Year, month, week format?

Anonymous — Wed, 27 Apr 2016 01:35:00 GMT

sample data

Re: Year, month, week format?

sunny_talwar — Wed, 27 Apr 2016 01:38:43 GMT

One more questions:

What is W, M, Y ? (Week, Month, Year?) How are you calculating these numbers? Most recent week, month, year?

Re: Year, month, week format?

sunny_talwar — Wed, 27 Apr 2016 01:51:53 GMT

Not sure, but is this what you want? Created this using Pivot Table

Re: Year, month, week format?

Anonymous — Wed, 27 Apr 2016 01:54:42 GMT

yes W M Y stands for week month year.

That looks good but are you able to add the week month as well?

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/>

Re: Year, month, week format?

sunny_talwar — Wed, 27 Apr 2016 01:58:03 GMT

How do you calculate Week, Month, Year? Most recent?

Re: Year, month, week format?

sunny_talwar — Wed, 27 Apr 2016 02:02:51 GMT

May be this:

Re: Year, month, week format?

Anonymous — Wed, 27 Apr 2016 02:03:06 GMT

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>

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

Re: Year, month, week format?

Anonymous — Wed, 27 Apr 2016 02:03:42 GMT

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>

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

Re: Year, month, week format?

Anonymous — Wed, 27 Apr 2016 02:12:42 GMT

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>

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

Re: Year, month, week format?

sunny_talwar — Wed, 27 Apr 2016 02:15:12 GMT

You can use a sort expression on the sort tab:

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

Re: Year, month, week format?

Anonymous — Wed, 27 Apr 2016 02:22:54 GMT

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>

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

Re: Year, month, week format?

sunny_talwar — Wed, 27 Apr 2016 02:26:18 GMT

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

Re: Year, month, week format?

Anonymous — Wed, 27 Apr 2016 02:34:54 GMT

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>

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

Re: Year, month, week format?

sunny_talwar — Wed, 27 Apr 2016 02:37:37 GMT

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)

Re: Year, month, week format?

Anonymous — Wed, 27 Apr 2016 05:13:47 GMT

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?

Re: Year, month, week format?

sunny_talwar — Wed, 27 Apr 2016 08:01:42 GMT

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