topic Re: Aggregating sales by date in QlikView

Aggregating sales by date

Thu, 22 May 2014 11:06:00 GMT

I am trying create a bar graph to aggregate sales data based on the date of the transactions (i.e. my date format is dd/mm/yyyy). I would like to view this data by different frequencies, i.e. aggregated on a month basis for the range of the data. What I have now sales showing for each individual date. I have tried using the AGGR , Group By functions but to no avail. Please help

Re: Aggregating sales by date

tresB — Thu, 22 May 2014 11:13:33 GMT

Create a Month field in the script like:

Load

DateField,

Month(DateField) as Month

nad then use this Month field as dimension in the chart, and expression as you want.

Re: Aggregating sales by date

Thu, 22 May 2014 11:18:16 GMT

Can you provide some sample data ??

Re: Aggregating sales by date

Thu, 22 May 2014 11:22:58 GMT

Thank you!

What if I need it by month and year as I have data from different years?

Re: Aggregating sales by date

Thu, 22 May 2014 11:33:27 GMT

In that case u can use this Script

Load

DateField,

Month(DateField) & '/' & Year(DateField) as Month/Year

and u can use "Month/Year" as Dimesion in chart

Re: Aggregating sales by date

Thu, 22 May 2014 11:46:41 GMT

or you use

Load

DataField,

Month(DateField) as Month,

year(DateField) as Year

you can use Month and Year now in a list box and it will calculate the data always for the selected year and month.

regards,

Re: Aggregating sales by date

Thu, 22 May 2014 11:50:12 GMT

Thanks! Seems like this should be something intuitive in the charting itself instead of having to code. For example you can do this easily in excel by using the group by function. Thank you for your help

Re: Aggregating sales by date

tresB — Thu, 22 May 2014 12:17:32 GMT

For that, you should generate field like:

Load

MonthName(DateName) as Month