11 Replies Latest reply: May 22, 2013 1:09 PM by Dinesh Kumar

weekdays of a date

i have a date column which gives every possible transaction date which is pretty much every day as i do have weekend days(saturday and sunday). The issue is when i show the count(transactions) i want to show the saturday and sundays in fridays count which really is the friday's count is Friday+saturday+sunday together as but show as friday's count. But i want this only friday where rest of the days will stay as is.

How can i show the 'Sat' and 'Sun' as fridays count?

• Re: weekdays of a date

Right now the way i am calculating the today count is

Sum(Max(Transac Date))- but when it's friday i want it to count sat and sun as well with the friday?

• Re: weekdays of a date

You can do this in your loadscript as follows:

If(Num(Weekday(Transac_Date)) >=5, 'Fri', Weekday(Transac_Date)) as Weekdays_NEW

For all days with a weekday number higher than 5 (friday) you'll set the weekday to 'Fri', otherwise the Weekday applies.

Hope this helps.

• Re: weekdays of a date

hi mrdaan, i tried to use this logic i get Fri twice but if i do that

If(Num(Weekday(Transac_Date)) >=4 then it will have the value Fri as anything greater than weekday>=4.

Thanks for the help, now how can i use this in the expression saying use this condition if the weekday is friday and for any other day just do sum(max(transac date))?

• Re: weekdays of a date

Either >=5 as I initially said or >4 is fine.

Please feel free to mark my answer as correct:)

• Re: weekdays of a date

i'd do that in a load

table:

weekname(transdate) as weekname,

if (weekday(trans_date)=5 or weekday(tran_date)=6 , 4) as WeekDay

and use set analysis

sum(weekday={'4'} total) but then  their weeknmae has to be the same. you need to adjust depending on your data is like. Hope this helps.

• Re: weekdays of a date

Thanks even this helps me to call Sat and Sun to Fri but now i am having problem using this logic implmented on the expression because the expression should consider two scenarious if its any day just sum(max(date)) but i didnt figure yet how can i calculate the friday as the calculation is based on date column?

• Re: weekdays of a date

I don't really understand what you want? Are you trying to compute the sum for the latest day in your dataset?

Anyway, I expect you are trying to achieve something else. If so you can  provably solve your issue if you change your dimension to Weekdays_NEW. Qlikview will now compute the sales for 'monday-friday'.

• Re: weekdays of a date

Hi mrdaan,

I have a Date column which is Transac date, it will be having much likely everyday and on the UI i am showing the max(Transac date)) for the latest date count and doing a sum on id's. But this is not a real time data so when i mean today wherein on the data perspective it is yesterday.

Example:

lets say today is monday so when i show the count i want for latest date i want to add fri+sat+sun as one business day and show the count but for the rest of days i just want to use the latest date.

sum({ <[Trans date]={"\$(=\$(vtoday))"} > } [Trans ID])

vtoday=date(max([Trans date])) --this is what i was doing earlier.

• Re: weekdays of a date

Hi Qlikview 08:

you could use Trans date > vtoday. And set vToday on today-1 for weekday 6 and today-2 for weekday 7.

Another way is to do the same thing in your loadscript: reset the date for weekday 6 as date-1 and for sunday as date-2. This way you'll hardcode the right date.

Yet nother way is to use a nested if statement in your counter, something like:

if(weekday(vtoday)=7,sum({ <[Trans date]={"\$(=\$(vtoday)-2)"} > } [Trans ID]),  if(weekday(vtoday)=6,sum({ <[Trans date]={"\$(=\$(vtoday)-1)"} > } [Trans ID]), sum({ <[Trans date]={"\$(=\$(vtoday))"} > } [Trans ID])

)))

Please don't just copy-paste but follow the logic as it's already quite late here.

Hope this finally solves your issue, let me know if it did.

• Re: weekdays of a date

or

==> First count weekday(Date)  ... This will return 1,2,3,4,5,6,7

==> Then  use Mapping table for WeekDayname

mapWeekDay:

Mapping Load * Inline [

weekday, Weekdayname

1          ,  Mon

2          ,  Tus

3          ,  Wed

4          ,  Thu

5          ,  Fri

6          ,  Fri

7          ,  Fri ];

Apply above mapping table on calculated field in first step.

• Re: weekdays of a date

it was helpful thanks mrdaan.