Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
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

Tags (2)
1 Solution

Accepted Solutions
sushil353
Honored Contributor II

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

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

11 Replies
sushil353
Honored Contributor II

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

Hi,

Please find the attached sample app.

Hope that helps you.

HTH

Sushil

Partner
Partner

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

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

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

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

Not applicable

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

sushil353
Honored Contributor II

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.

Please see the app..

Not applicable

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?

sushil353
Honored Contributor II

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.

Not applicable

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?