Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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)
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
Hi,
Thanks for help! I'm not able to do it from script. Tried option2, but still 0.
can you please share your sample Qvw?
Is your Month column a numeric field? If it's formatted as Jan, Feb, Mar (...), it won't work.
Sorry...I can't
No it's not numeric. Can I use num()?
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.
Hi William,
I tried with if(Month<month(today()),Month), it works. So I think that might not be the reason...
Interesting.. how about this then?
Sum({$<Month={'$(=Month(Today()))'}>} Sales)