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: 
wanyunyang
Creator III
Creator III

sum up all sales before current month

I have recent 3 years sales data, I'm trying to sum up all sales before August (current month) in these years. My function is:

Sum({<Month={"<(=month(today()))"}>}Sales)

The result is 0, I have no idea what's wrong. Any ideas?

Thanks in advance!!

1 Solution

Accepted Solutions
sunny_talwar

May be create a new field in the script like this

Num(Month(Date)) as NumMonth

and then try this

Sum({<NumMonth = {"<$(=Num(Month(Today())))"}>}Sales)

View solution in original post

23 Replies
vamsee
Specialist
Specialist

Hello,

You can do this two ways.

Option1:

Create a column called year_month in the script like Year &'|'&Month as Year_Month.

Create a flag if( Year_Month < (Year(Today()) &'|'& Month(Today()) ),1, 0) as Year_Month_Flag.

In the chart SUM({<Year_Month_Flag ={1} >} Sales )

Option2: SUM( {< Month = {"=$(<=Month(Today) )"}, Year ={"=$(<= Year(Today() ) ) "} >} Sales )

Edited: Typo error. Please use single quotes in &'|'& not double. My bad

wanyunyang
Creator III
Creator III
Author

Hi,

Thanks for help! I'm not able to do it from script. Tried option2, but still 0.

vamsee
Specialist
Specialist

can you please share your sample Qvw?

william_fu
Creator II
Creator II

Is your Month column a numeric field? If it's formatted as Jan, Feb, Mar (...), it won't work.

wanyunyang
Creator III
Creator III
Author

Sorry...I can't

wanyunyang
Creator III
Creator III
Author

No it's not numeric. Can I use num()?

vamsee
Specialist
Specialist

Yeah if in that case try this for Option 1.

LOAD *,
IF(Date#(Year_Month, 'YYYY|M') < Date#( ( Year(Today())&'|' & NUM(Month(Today())) ), 'YYYY|M'), 1,0) as Year_Month_Flag;
LOAD  Year(Date#(Year, 'YYYY')) as Year,
NUM(Month(Date#(Month, 'MMM'))) as Month,
Year(Date#(Year, 'YYYY')) &'|' &  NUM(Month(Date#(Month, 'MMM')))  AS Year_Month;

Note: You can completely remove the delimiter in all columns too.

wanyunyang
Creator III
Creator III
Author

Hi William,

I tried with if(Month<month(today()),Month), it works. So I think that might not be the reason...

william_fu
Creator II
Creator II

Interesting.. how about this then?

Sum({$<Month={'$(=Month(Today()))'}>} Sales)