topic Re: last 2 months using where clause in QlikView

last 2 months using where clause

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

Hi,

I am trying to do calculation in pivot to show only last 2 month data.

My Dimension is Month and

I need Expressions as Count of Distinct (ID) data only When

1. [Actual End Date] <= [Gate Schedule End Date]

2. Month of [Actual End Date] should be last 2 month ( Eg Sep & Oct )

3. Country = 'India'

4. PASS/FAIL = 'PASS'

Re: last 2 months using where clause

Anil_Babu_Samineni — Tue, 07 Nov 2017 11:40:15 GMT

Perhaps this, Haven't test.

Count({<[Actual End Date] = {"=Only({<Month = {">=$(=AddMonths(Max(Month), -1) <=$(=Max(Month)))"}>} [Actual End Date]) <= [Gate Schedule End Date]"}, Country = {"India"}, [PASS/FAIL] = {'PASS'}>} DISTINCT Data)

Re: last 2 months using where clause

DipeshVadgama — Tue, 07 Nov 2017 11:47:05 GMT

Thanks, But It does not works

Re: last 2 months using where clause

prat1507 — Tue, 07 Nov 2017 12:20:57 GMT

Please find attached the required app.

Regards

Pratyush

Re: last 2 months using where clause

DipeshVadgama — Tue, 07 Nov 2017 13:19:55 GMT

Thank Pratyush,

Request you to please send me syntax, I can not open file.

Re: last 2 months using where clause

prat1507 — Tue, 07 Nov 2017 13:55:02 GMT

I've used this as my dimension and suppressed the value NULL

=Upper(If(Date([Actual End Date])>=MonthStart(AddMonths(Today(),-2)) and Date([Actual End Date])<MonthStart(AddMonths(Today(),0)) and [Actual End Date]<[Gate Scheduled End Date],

Date([Actual End Date],'MMM-YY')))

and in the expression I'm using ;

=Count({<Country={'India'},[PASS/FAIL]={'PASS'}>}ID)

Re: last 2 months using where clause

DipeshVadgama — Tue, 07 Nov 2017 14:56:45 GMT

Thanks buddy it helped me.

Re: last 2 months using where clause

prat1507 — Tue, 07 Nov 2017 15:09:26 GMT

Glad to help

Re: last 2 months using where clause

sunny_talwar — Tue, 07 Nov 2017 15:22:16 GMT

Using prat1507‌'s sample attached, I propose another solutions

Dimension

DTM

Expression

=Count({<Country={'India'},[PASS/FAIL]={'PASS'}, DTM = {">=$(=Date(MonthStart(Today(), -2), 'MMM-YY'))<=$(=Date(MonthStart(Today()), 'MMM-YY'))"}>} If([Actual End Date]<=[Gate Scheduled End Date], ID))

Here I modified the DTM that Pratyush created like this

Tab:

LOAD ID,

[PASS/FAIL],

[Gate Actual Start Date],

[Gate Scheduled Start Date],

[Gate Scheduled End Date],

[Actual End Date],

[Project Scheduled Start Date],

[Project Actual Start Date],

[Project Actual End Date],

[Project Scheduled End Date],

Country,

Date(MonthStart([Actual End Date]),'MMM-YY') as DTM,

If([Actual End Date]<=[Gate Scheduled End Date], 1, 0) as Flag

FROM

[Test File.xls]

(biff, embedded labels, table is Sheet1$);

I also created a flag, but was not sure if these two dates come from the same file or not... if they are, then creating this flag might come in very handy as you will be able to then use this expression

=Count({<Country={'India'},[PASS/FAIL]={'PASS'}, DTM = {">=$(=Date(MonthStart(Today(), -2), 'MMM-YY'))<=$(=Date(MonthStart(Today()), 'MMM-YY'))"}, Flag = {1}>} ID)

I personally prefer to use set analysis over calculated dimensions, but there are times when the calculations dimension come in handy.

Best,

Sunny