How to write group by function in qlikview

[Anonymous]

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

[prma7799]

Your question confusing too much ....

Please share some sample with expected output.

[#3]

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

[Anil_Babu_Samineni]

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)

[Anonymous]

In back end with group by query month and year.

Op

Month Year Sales

Jan     2015  200

March  2016 300

[Anil_Babu_Samineni]

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