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

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?

11 Replies
Not applicable
Author

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?

Not applicable
Author

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.

alexpanjhc
Specialist
Specialist

i'd do that in a load

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

Not applicable
Author

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))?

Not applicable
Author

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

Please feel free to mark my answer as correct:)

Not applicable
Author

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?

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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.