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

    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
                Alex Pan

                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.

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