Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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!

Thanks in Advance

J

1 Solution

Accepted Solutions
sushil353
Master II
Master II

use CTRL+ALT+V.

1.click on Add

2. type the variable name

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

if your problem is resolved then make this thread Answered.

Thanks

Sushil

View solution in original post

11 Replies
sushil353
Master II
Master II

Hi,

Please find the attached sample app.

Hope that helps you.

HTH

Sushil

nirav_bhimani
Partner - Specialist
Partner - Specialist

Hi,

Try this Expression.

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

Regards,

Nirav Bhimani

alexandros17
Partner - Champion III
Partner - Champion III

Table1:

LOAD Type,

    
FinalDate,

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

    
[Net Sales],

    
year(AddMonths(FinalDate,-1))*100 + month(AddMonths(FinalDate,-1)) as previousyearmonth

FROM



(
ooxml, embedded labels, table is Foglio1);



Left Join



LOAD

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

Not applicable
Author

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

Not applicable
Author

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.....

sushil353
Master II
Master II

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.

Please see the app..

Not applicable
Author

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

sushil353
Master II
Master II

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.

Not applicable
Author

Thanks Sushil, This makes perect sense! Thank You!

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