topic Re: expression required in QlikView

expression required

yoganantha321 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: expression required

pooja_prabhu_n — Fri, 07 Sep 2018 07:20:37 GMT

Hi,

What is the logic to find the Future months calculation.

Can you post some sample data with required output.

Thanks,

Pooja

Re: expression required

yoganantha321 — Fri, 07 Sep 2018 07:31:12 GMT

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

Re: expression required

yoganantha321 — Fri, 07 Sep 2018 07:40:39 GMT

Pooja,

I have attached the sample data above

Re: expression required

pooja_prabhu_n — Fri, 07 Sep 2018 07:43:10 GMT

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;

Re: expression required

yoganantha321 — Fri, 07 Sep 2018 08:31:33 GMT

I have attached sample data

Re: expression required

pooja_prabhu_n — Fri, 07 Sep 2018 11:13:38 GMT

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

Re: expression required

yoganantha321 — Mon, 10 Sep 2018 05:12:26 GMT

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