topic Re: How to write group by function in qlikview in QlikView

How to write group by function in qlikview

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have data with Date format 2/2/2015 to 2/2/2017 and sale for all these year ( month and year,sale) and i want o/p month year sales.

Thanks

Re: How to write group by function in qlikview

prma7799 — Wed, 21 Jun 2017 06:41:22 GMT

Your question confusing too much ....

Please share some sample with expected output.

Re: How to write group by function in qlikview

Wed, 21 Jun 2017 06:45:11 GMT

Hello,

create a master calendar similar to this one:

Temp:

Load

min(OrderDate) as minDate,

max(OrderDate) as maxDate

Resident Orders;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate AS OrderDate,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Just rename the OrderDate with your date field name. and rename Orders table by the name of your table ,

Reload, and the calendar will be linked to your table .

Then in your dashboard just create a table with the dimension : MonthYear and as expression : sum(sales)

Let me know if you need any additional help!

Rima

Re: How to write group by function in qlikview

Anil_Babu_Samineni — Wed, 21 Jun 2017 06:46:04 GMT

May be create

Load DateField, Sales, Month(DateField) as Month, Year(DateField) as Year, MonthName(DateField) as MonthYear

From Table;

Then create report straight table

Dim - MonthYear

Expression - Sum(Sales)

Re: How to write group by function in qlikview

Anonymous — Wed, 21 Jun 2017 07:05:59 GMT

In back end with group by query month and year.

Month Year Sales

Jan 2015 200

March 2016 300

Re: How to write group by function in qlikview

Anil_Babu_Samineni — Wed, 21 Jun 2017 07:09:03 GMT

You wish?

Load DateField, Sales, Month(DateField) as Month, Year(DateField) as Year, MonthName(DateField) as MonthYear

From Table Group By Month(DateField), Year(DateField);