Facing probelm in Group by clause

[tripatirao]

Hi Experts,

I want to find the current month minus previous month data based on month and region.

I am getting  the required output based on month which is shown in excel  but When I am adding region field required output

is not coming.

I have attached source file and qlikview  file both.

I hope I can get the solution by using group by clause but not able to implement here

Please help me to find out the solution.

Regards

Tripati

[its_anandrjs]

Try to load your table this ways

Data:

LOAD Region,

     Date,

     Sales

FROM

(ooxml, embedded labels, table is Sheet1);

Dime:

LOAD

Date,

Month(Date) as Month,

If( Date = MonthStart(Date(Today())),1,

If( Date = MonthStart( AddMonths( Date(Today()),-1)),2,

0)) as Flag

Resident Data;

In the Pivot Table

Dim1:- Region

Dim2:- Month

//For Current month

Expre1:- Sum({<Flag={1}>} Sales)

//For previous month

Expre2:- Sum({<Flag={2}>} Sales)

Check the attached one also

Regards

Anand

[tripatirao]

Dear Anand,

Thanks for quick response.

My requirement is to  find the current month sales - previous month sales irrespective of financial year which I am getting If I load excel without region I am the output which is shown in attached  qlikview file

But now I want  to add another column region and I want  to find the current month sale- previous month sale

by both region and sales wise.

I need to do this backend and wants to show tablebox.

I have attached the excel which has no region and qlikview file which has output value

[tripatirao]

Dear Anand,

Forget to attached the excel file.

Please find the excel file.

Regards

Tripati

[sunny_talwar · Accepted solution]

Try this may be:

Data:

LOAD *,

  Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year

  Dual(Month, fMonth)  as FMonth,

  num(MakeDate(fYear,fMonth)) as Date1;          // Dual fiscal month)

LOAD *,

  Year + If(Month>=$(vFM), 1, 0) as fYear,

  Mod(Month-$(vFM), 12)+1 as fMonth;

LOAD Date,

  Region,

  Month(Date) as Month,

  Year(Date) as Year,

  Sales

FROM [YTD.xlsx]

(ooxml, embedded labels, table is Sheet1);

Data2:

LOAD Region,

  FYear,

  FMonth,

  fYear,

  fMonth,

  Date1,

  Month,

  Year,

  Sum(Sales) as Sales

Resident Data

Group by Region, FYear, FMonth, fYear, fMonth, Date1, Month, Year;

DROP Table Data;

Data3:

Load RowNo(),

  Region,

  FYear,

  FMonth,

  fYear,

  fMonth,

  Month,

  Date1,

  Year,

  'hi' as Tend,

  Sales,

  If(Region = Previous(Region), RangeSum(Sales, -Peek('Sales')), Sales) as CurrentMonthValue1,

  If(RowNo() = 1, RangeSum(Sales, 0),

  If(Peek(Date1) <> Date1, -1*(RangeSum(Peek(Sales),0)-numsum(Sales)),RangeSum(Sales,0))) as CurrentMonthValue

Resident Data2

Order By Region, Date1 asc;

[tripatirao]

Dear Sunny,

Good support as always.

Thanks you so much

if I want previous month -current month  data how I have to do.

[sunny_talwar]

Are you saying that if you want to do Previous - Current instead of Current - Previous what you would need to do? May be this:

If(Region = Previous(Region), RangeSum(Sales, -Peek('Sales')), Sales) as CurrentMonthValue1,

In the above portion of the script the word Sales is current month and Peek('Sales') is previous month. If you want to do Previous - Current, just change the order of things here

If(Region = Previous(Region), RangeSum(Peek('Sales'), -Sales), Sales) as CurrentMonthValue1,

Is that what you wanted? or did I misunderstood?

[its_anandrjs]

Sorry i was busy on some task and you got the correct also.

Regards

Anand