topic Re: Set Analysis and AGGR and IF Function not working... in QlikView

Set Analysis and AGGR and IF Function not working...

Wed, 08 Oct 2014 15:05:58 GMT

Dear all,

I am new to Qlikview and currently having Problems with the If/AGGR/ and Set Analysis.

In my case I have the following Sales-Data from one month as a Table:

Vendor, Date, Quantity, Sales and so on...

What I want to do, is getting the average Quantities per Day during one month per Vendor.

So something like: sum of Quantities of August 2014 / Number of Working Days of the Vendor

For this I will need at first the amount of Days that the Vendor had orders. For this I used the Count function in a Pivot table as an Expression:

Count (Distinct Date)

But now comes the difficulty... Orders can also be placed on a Saturday or Sunday, but these days should not be included as they do not belong to the working days (Mon - Fri). So I only want to count the distinct Dates of the month during Mon - Fri where every Vendor sold a product...

I have a master calendar where the function "weekday(Date)" delivers the weekday in a short form in german (e.g. "So" , "Sa" ...)

With that I tried the following Expression in a Pivot table:

if(weekday(Date) <> 'So', if(weekday(Date) <> 'Sa', Count(distinct (Date)))

I get no error on the expression, but the result is still all distinct days including Saturday and Sundays...

Then I tried:

aggr(Count({<weekday(Date) -={'So','Sa'}>} distinct Date), Vendor)

The Expression has again "no error" but this time I cant get any results with it...

Then I tried to create another column with "1" for Workday and "0" for Weekends:

if(weekday(Date) <> 'So', if( weekday(Date) <> 'Sa', 1,0),0)

This worked for the rows when I showed the Date Column in the Pivot Table, but when I tried to sum it afterwards.. again I received the total days including the Weekend.

I hope someone can understand my issue here and help me out to get average sales based on the amount of working days!

Thanks in advance!!

Re: Set Analysis and AGGR and IF Function not working...

Anonymous — Wed, 08 Oct 2014 16:28:52 GMT

I think that is better to you create on your load a field with the weekDay, and use it on the set analysis. I'm not sure if Set Analysis works using something like WeekDay(Date).

So, this way you could use something like this on your count:

=count({<Week_Day-={'so','sa'}>} Distinct Date)

Could you attach here some qvw file with sample data or an spreadsheet with the data that you want to load? It would be easier find the problem.

Regards,

Gabriel

Re: Set Analysis and AGGR and IF Function not working...

arsal_90 — Wed, 08 Oct 2014 16:55:07 GMT

Try to use his function

networkdays (start:date, end_date {, holiday})

Re: Set Analysis and AGGR and IF Function not working...

Thu, 09 Oct 2014 06:19:08 GMT

Dear all,

thanks für your quick replys!!

@ Gabriel

Sorry, I'm not able to upload any data at the moment... it sounds good with the load thing, but as I am new to QV I dont know how to do this... where in the script should I do this? And how?

@ Muhammad

This function looks nice, but how do I get the start and end date to be the selected month?

Any other ideas so far?

Thanks again!!

Re: Set Analysis and AGGR and IF Function not working...

Thu, 09 Oct 2014 06:26:35 GMT

@ muhammad

One more thing... will this function recognize when vendor A has only 20 days where he sold products and vendor B has 17? Or will it just give the distinct number of days over all vendors? (which is not what I need...)

thx

Re: Set Analysis and AGGR and IF Function not working...

Anonymous — Thu, 09 Oct 2014 10:29:21 GMT

To create a week field (named Week_Day) on the LOAD, you just use this on the same LOAD that you bring your Date:

WeekDay(Date) as Week_Day

Re: Set Analysis and AGGR and IF Function not working...

Thu, 09 Oct 2014 13:18:24 GMT

Thanks, that worked so far!!! I will test this with a larger amount of data including several month...

I would still like to know if the networkdays function can get the start and enddate from the selection of the master calendar...

thanks again for your help!!