expression required

[yoganantha321]

I have a straight table with column month which has Jan-Dec. (PFA)

I need to sum the value which is below current month and future months as two expressions.

Thanks in advance.

Regards,

Yoganantha Prakash G P

I have attached sample data too.......

expected output:

for Project A

Till current Month  0.02

Future Month 0.06

Balance 0.05 {(budget-currentMonth-Future Month) must be  (0.13 (Budget for project A)-0.02-0.06)}

for Project B

Till current Month 0

Future Month 0

Balance 0.02

[pooja_prabhu_n]

Hi,

What is the logic to find the Future months calculation.

Can you post some sample data with required output.

Thanks,

Pooja

[yoganantha321]

Future month  calculation logic is current month is september future month is oct, nov, Dec

[yoganantha321]

Pooja,

I have attached the sample data above

[pooja_prabhu_n]

Hi,

You can generate the future months in the script like below:

Temp: 

Load 

             MonthName(Today()) as minDate, 

               MonthName(AddMonths(MonthName(Today()),3)) as maxDate          

; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

               TempDate AS Date, 

               week(TempDate) As Week, 

               Year(TempDate) As Year, 

               MonthName(TempDate) As Month

Resident TempCalendar

Order By TempDate ASC; 

Drop Table TempCalendar;

[yoganantha321]

I  have attached sample data

[pooja_prabhu_n · Accepted solution]

Hi,

Convert month column in text format to number format.

Below current Month: =Sum({<Month={"<=$(=Monthstart(Today()))"}>} Value)

Future Month: =Sum({<Month={">$(=Monthstart(Today()))"}>} Value)

Hope this helps

Thanks,

Pooja

[yoganantha321]

I have tried like this

for loading script:

Num(Month(Date(Date#(Month,'MMM'),'MM'))) as Temp

Expression:

=Sum({<Temp={"<=$(=Num(Month(Today())))"}>} Value)

Can you help