Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

week formatting: apr 6th - apr 12th

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

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


talk is cheap, supply exceeds demand
Not applicable
Author

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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

Not applicable
Author

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


talk is cheap, supply exceeds demand
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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