Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

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?

Not applicable

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.

alexpanjhc
Valued Contributor

Re: weekdays of a date

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

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

Not applicable

Re: weekdays of a date

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

Please feel free to mark my answer as correctSmiley Happy

Not applicable

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?

Not applicable

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

Not applicable

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.

Not applicable

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.

Community Browser