How to show sales for the month in text object

[ashis]

Hello,

I want to show sales for selected month and previous month in two object objects.

I have month field in a list box.

I am writing the following set expression however not getting any data:

=sum({<Month={$(=Max(Month))}>}Sales)  and

=sum({<Month={$(=Max(Month)-1)}>}Sales)

Month list box lists the months as Jan, Feb, Mar...etc.

Also,If i want to show selected month and its previous month in text object what would be the express.

I tried the following expression it seems not working

vCurrentMonth=date(date#(Max(Month),'YYY'),'YYY')

vPreviousMonth=date(date#(Max(Month)-1,'YYY'),'YYY')

Please suggest.

Thank you,

[ashis]

Hi,

I have master calendar and fields are as below:

MasterCalendar: 
Load 
TempDate AS ShipDate, 
week(TempDate) As Week, 
Year(TempDate) As Year, 
Month(TempDate) As Month,
num(Month(TempDate)) As NumMonth,
Day(TempDate) As Day, 
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
WeekDay(TempDate) as WeekDay 
Resident TempCalendar 
Order By TempDate ASC; 

I have both Month and NumMonth, one is text and one is numeric.

I tried the following expression as well but not getting correct output

=sum({<NumMonth={"=max(NumMonth)-1"}>}Sales)

[Anil_Babu_Samineni]

Use this?

=sum({<NumMonth={"$(=max(NumMonth))-1"}>}Sales)

Re: How to show sales for the month in text object

[ashis]

No, getting 0.0 as value.

[Kushal_Chawda]

try this

=sum({<NumMonth={"$(=max(NumMonth))"}>}Sales)   <- Current

=sum({<NumMonth={"$(=max(NumMonth)-1)"}>}Sales) <- Previous

[ashis]

This should work however in my case I am not getting value.

[ashis]

Hi,

I am using the sales data that comes with the default installation with qlikview, and created one master calendar . it looks like the following:

QuartersMap: 
MAPPING LOAD  
rowno() as Month, 
'Q' & Ceil (rowno()/3) as Quarter 
AUTOGENERATE (12); 
     
Temp: 
Load 
min(ShipDate) as minDate, 
max(ShipDate) 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 ShipDate, 
  week(TempDate) As Week, 
  Year(TempDate) As Year, 
  Month(TempDate) As Month,
  Date(MonthStart(TempDate),'MMM-YY') as MMonth,
  num(Month(TempDate)) As NumMonth,
  Day(TempDate) As Day, 
  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; 

Set vLastMonth=      "=Date(Max(MMonth),'MMM-YY')";

Set v2ndLastMonth= "=Date(AddMonths(Max(MMonth),-1),'MMM-YY')";

Now , I want to show sales of current selection on Month as well as previous month's sales in text object.

I am using following expressions, but not getting any data:

=Sum( {$<Month={"$(v2ndLastMonth)"}>} Sales )
=Sum( {$<Month={"$(vLastMonth)"}>} Amount )

Please help.
Thank you,
Ashis

[ashis]

Both the expression is giving the same value.

Thank you,

Ashis

[ashis]

I have resolved the issue, changed the month field in master calender

as Date(MonthStart(TempDate),'MMM-YY') as Month,

and used this month in my expression , and it gives the correct result.

Thank you all for your quick reply.

Regards,

Ashis