topic Re: Set Analysis - Current Selection with End of Month date of all previous months in QlikView

Set Analysis - Current Selection with End of Month date of all previous months

Anonymous — Fri, 07 Jul 2017 19:10:12 GMT

What we are looking for is , Pivot chart should display the selected date plus the EOM date of previous months

If we have the dataset as below :

Load Date Credit Rate

05/01/2017 720

05/02/2017 710

05/03/2017 680

05/04/2017 540

04/04/2017 800

04/01/2017 820

03/31/2017 740

01/17/2017 728

01/28/2017 852

01/31/2017 738

Filter as below and the user selected "04/04/2017" from the filter

05/01/2017

05/02/2017

05/03/2017

05/04/2017

04/04/2017

04/01/2017

03/31/2017

01/17/2017

01/31/2017

Pivot chart should display as below (the selected date plus the End of the Month date of previous months)

Load Date Credit Rate

04/04/2017 800

03/31/2017 740

01/31/2017 738

Re: Set Analysis - Current Selection with End of Month date of all previous months

Anil_Babu_Samineni — Sat, 08 Jul 2017 03:28:54 GMT

Can you create one flag and use below expression. I am not testing now. But try from your end

Load *, if([Load Date]=Date(Floor(MonthEnd([Load Date]))),1,0) as MonthEnd_Flag Inline [

....

];

=sum({<MonthEnd_Flag = {1}, [Load Date]={$(=concat(chr(39) & [Load Date] & chr(39),',') )}>}[Credit Rate])

Re: Set Analysis - Current Selection with End of Month date of all previous months

antoniotiman — Sat, 08 Jul 2017 06:54:29 GMT

Hi,

try this Expression

If(Only({<[Load Date]={'<=$(=Max([Load Date]))'}>} [Load Date]) = Date(Floor(MonthEnd(Only({<[Load Date]={'<=$(=Max([Load Date]))'}>} [Load Date]))))
or [Load Date]=Max([Load Date]),Sum({<[Load Date]={'<=$(=Max([Load Date]))'}>} ([Credit Rate])))

Regards,

Antonio

Re: Set Analysis - Current Selection with End of Month date of all previous months

Anonymous — Fri, 14 Jul 2017 13:38:34 GMT

Thanks to everyone for the support.

I have resolved this issue using an workaround.

I have Used Group By Function at the load script level to get the Max(Load Date) for every month