Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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

Tags (1)
9 Replies

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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