week formatting: apr 6th - apr 12th

[#1]

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

[Gysbert_Wassenaar]

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

[#3]

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.

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

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

[Gysbert_Wassenaar]

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.

[#5]

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

[#6]

Hi,

I am using code like this,

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

& ' - '

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

AS Week;

As

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

[Gysbert_Wassenaar]

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

[#8]

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

[#9]

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

[#10]

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