11 Replies Latest reply: Mar 8, 2013 6:17 AM by qlik_beginner

# Sum of Net Sales from Max date from previous month...

Hi All,

I'm trying to complete the requirement on the attached excel.

Any help would be greatly appreciated!

J

• ###### Re: Sum of Net Sales from Max date from previous month...

Hi,

Please find the attached sample app.

Hope that helps you.

HTH

Sushil

• ###### Re: Sum of Net Sales from Max date from previous month...

Thanks Sushil, However the formula would work only on a one-off basis. For example if I was to select another date say 15/02/13, the previous month would then be January. I would need the figures to automatically change to the max date in Jnaury without having to amend the formula.....

• ###### Re: Sum of Net Sales from Max date from previous month...

If u checked the app.. i think it is working fine. as u have another filter type='STUB'

then when u are selecting 2/15/2013 it is returning 6 which is value of 1/25/2013.

• ###### Re: Sum of Net Sales from Max date from previous month...

Thanks Sushil: So you say If I create the 3 variables and retest on your app it should work?

• ###### Re: Sum of Net Sales from Max date from previous month...

Let me explain you solution logic;

1. first variable will hold monthstartdate of previous month

2. Second variable will hold the monthenddate of previous month

3. Third vaiable will hold the max date of the previous month date range

and then finally you can have sum of max date of previous month.

I used variable for this for simplicity and understanding..

Feel free if u still have confusion.

• ###### Re: Sum of Net Sales from Max date from previous month...

Thanks Sushil, This makes perect sense! Thank You!

However: Not sure how to creaste & SAVE a variable?

• ###### Re: Sum of Net Sales from Max date from previous month...

use CTRL+ALT+V.

2. type the variable name

3. click on variable created and put the expression in the definition box.

Thanks

Sushil

• ###### Re: Sum of Net Sales from Max date from previous month...

Thank you so much

• ###### Re: Sum of Net Sales from Max date from previous month...

Hi,

Try this Expression.

=(sum({<FinalDate = {">=\$(=date(Yearstart(max(FinalDate))))<=\$(=max(FinalDate))"}, Type={ 'STUB'},month=>}[Net Sales]))

Regards,

Nirav Bhimani

• ###### Re: Sum of Net Sales from Max date from previous month...

Hi Nirav: Thanks for your time and attempt. However this did not work...... :-(

• ###### Re: Sum of Net Sales from Max date from previous month...

Table1:

FinalDate,

year(FinalDate) * 100 + month(FinalDate) as yearmonth,

[Net Sales],

FROM

[C:\Users\samsung\Desktop\MaxDate-Previous Month.xlsx]

(
ooxml, embedded labels, table is Foglio1);

Left Join

Type,

yearmonth as previousyearmonth,

max(FinalDate) as maxPrev

Resident Table1

group by Type, yearmonth;

This code consider previous month only as you need ...

Hope it helps