topic Re: week formatting: apr 6th - apr 12th in App Development

week formatting: apr 6th - apr 12th

Thu, 30 Apr 2015 05:57:04 GMT

Hi,

I need format the weekstart and weekend like "apr 6th - apr 12th" and i need to select the data on week basis in QLIK SENSE.

for ex: i need to select the data of last 4 weeks and display in bar chart with name like above of particular week.

please help me to do this.

Thanks,

Pramod

Re: week formatting: apr 6th - apr 12th

Gysbert_Wassenaar — Fri, 01 May 2015 08:39:43 GMT

Try something like this:

MapDay:
MAPPING LOAD * INLINE [
Key, Value
1, st
2, nd
3, rd
21, st
22, nd
30, st
31, st
];

Data:
LOAD *,
  lower(Month(WeekStart) & ' ' & WeekStartDay & ApplyMap('MapDay',WeekStartDay, 'th')
  & ' - '
  & Month(WeekEnd) & ' ' & WeekEndDay & ApplyMap('MapDay',WeekEndDay, 'th'))
  AS Week;
LOAD [MyDate],
  Month([MyDate]) as Month,
  Day([MyDate]) as Day,
  WeekStart([MyDate]) as WeekStart,
  WeekEnd([MyDate]) as WeekEnd,
  Day(WeekStart([MyDate])) as WeekStartDay,
  Day(WeekEnd([MyDate])) as WeekEndDay,
    ... other fields....
FROM ...source....

Re: week formatting: apr 6th - apr 12th

Fri, 01 May 2015 09:40:30 GMT

hi Gysbert Wassenaar,

As I am new to qlik sense and please explain briefly. and i am using the field 'Date_created' in table to extract the data.

MAPPING LOAD * INLINE [
Key, Value
1, st
2, nd
3, rd
];

Dint got why the above code is using and what is that "st, nd and rd".

If you don't mind please explain it briefly.

thanks,

Pramod

Re: week formatting: apr 6th - apr 12th

Gysbert_Wassenaar — Fri, 01 May 2015 09:51:28 GMT

The mapping table MapDay is used by the ApplyMap function to change the day number into the day number appended with st, nd, rd or th. So that for example apr 1 becomes apr 1st.

Re: week formatting: apr 6th - apr 12th

Fri, 01 May 2015 10:02:28 GMT

Hi,

Used the code u have given I am getting value like below please help.

Feb 42055.809236111th - Feb 42055.809236111th

Please help to correct it.

Thanks,

pramod

Re: week formatting: apr 6th - apr 12th

Fri, 01 May 2015 10:12:40 GMT

Hi,

I am using code like this,

lower(Month(WeekStart) & ' ' & WeekStartDay & ApplyMap('MapDay',WeekStartDay, 'th')

& ' - '

& Month(WeekEnd) & ' ' & WeekEndDay & ApplyMap('MapDay',WeekEndDay, 'th'))

AS Week;

lower(Month(date_created) & ' ' & date_created & ApplyMap('MapDay',date_created, 'th')

& ' - '

& Month(date_created) & ' ' & date_created & ApplyMap('MapDay',date_created, 'th'))

AS Week1,

If I use (WeekStart) and (WeekEnd) it’s showing error.

Please check and confirm. I have doubt in this part.

Thanks,

Pramod

Re: week formatting: apr 6th - apr 12th

Gysbert_Wassenaar — Fri, 01 May 2015 10:14:38 GMT

Looks like you're confusing a Day field with a Date field. Make sure to use WeekStartDay and WeekEndDay fields

Re: week formatting: apr 6th - apr 12th

Fri, 01 May 2015 10:42:04 GMT

Hi Gysbert Wassenaar,

Thanks a lot. it's working fine.

I have one more doubt. i need to show the data of one year in line chart.

The data points must be only quarterly. Please help

Regards,

Pramod

Re: week formatting: apr 6th - apr 12th

Fri, 01 May 2015 10:53:38 GMT

Hi Gysbert Wassenaar,

For above question in my database i have data of 3 years but i need only the weeks of 2015.

what script should i use to separate them?

please help.

Thanks,

Pramod

Re: week formatting: apr 6th - apr 12th

Fri, 01 May 2015 11:14:42 GMT

hi,

one more bug i am getting from above code is month repetition like "apr 28 - apr 4". instead of may 4 it's showing apr 4.

would you please help me to correct it.

Thanks,

Pramod