topic Re: Restricting bars with selection in QlikView

Restricting bars with selection

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

Hi Team,

I have Bar Chart as shown below

When i select Month Oct-17 from list box chart should display

Actual---should display bars till Oct-2017(Since i am giving selection as oct-2017)

LE----should display all 12 bars(Irrespective of selections) Dont have any value so it is showing nothing in chart

Budget----should display all 12 bars(Irrespective of selections)

All bars will be having accumulated value

I am able to control budget bar by bypassing Datemonth in exprression, but not able to do for Actual

Expression-

Actual=Num(Rangesum(Above(Sum({<DateMonth ={">=$(vE2Estartdate) <=$(vE2Eenddate)"}>}Actual),0,12)),'##.###')

Please help me on this

Thanks in ADVANCE

Re: Restricting bars with selection

OmarBenSalem — Tue, 28 Nov 2017 10:25:55 GMT

what if u alter it as folow:

=Num(Rangesum(Above(Sum({<DateMonth =,DateMonth ={">=$(vE2Estartdate) <=$(vE2Eenddate)"}>}Actual),0,12)),'##.###')

and ps: there is not need to use the num to format it; you have an entire time in where u can format ur measures like u want (easily)

Re: Restricting bars with selection

qlik4asif — Tue, 28 Nov 2017 10:33:30 GMT

Omar Thanks for reply,

But it wont take selection , if i bypass the month in expression

The problem is ,let us say when i select month oct-17,

bars are showing correct value till oct-2017 but for nov and dec it is showing same value as oct

This is because i am doing accumulation (Check my expression)

I need Actual as 0 for Nov and Dec (Considering Oct as my selection)

Thanks

Re: Restricting bars with selection

Anil_Babu_Samineni — Tue, 28 Nov 2017 10:36:14 GMT

This won't work at all due to single same filed you are degrading 2 times. That not the Qlikview behavior.

Re: Restricting bars with selection

Anil_Babu_Samineni — Tue, 28 Nov 2017 10:36:27 GMT

Can you define the variables you used for?

Re: Restricting bars with selection

YoussefBelloum — Tue, 28 Nov 2017 10:43:32 GMT

Hi,

if you have this on your variable:

vE2Estartdate = min(date)

change it to :

vE2Estartdate = min({1}date)

Re: Restricting bars with selection

OmarBenSalem — Tue, 28 Nov 2017 10:59:39 GMT

I see, than change ur expession like thing like this:

I would be :

if(Sum({<DateMonth ={">=$(vE2Estartdate) <=$(vE2Eenddate)"}>}Actual)<>0,

Rangesum(Above(Sum({<DateMonth ={">=$(vE2Estartdate) <=$(vE2Eenddate)"}>}Actual),0,12)),null())

per analogy :

if it's like this: same as you:

RangeSum(Above(Sum({<Year={"<=$(=max(Year))"}>}[Car sales]),0,RowNo()))

result:

while as follow:

if(sum({<Year={"<=$(=max(Year))"}>}[Car sales])<>0,

RangeSum(Above(Sum({<Year={"<=$(=max(Year))"}>}[Car sales]),0,RowNo())),Null()):

Re: Restricting bars with selection

qlik4asif — Tue, 28 Nov 2017 12:30:06 GMT

vE2Eenddate

=Max({<DummyKPI={'Production Cost YTD'},PC_YTD={">0"}> + <DummyKPI={'Production Cost YTD'},PC_YTD={"<0"}>}DateMonth)

vE2Estartdate

=Date(AddMonths(vE2Eenddate,-11),'YYYY-MM')

Re: Restricting bars with selection

OmarBenSalem — Tue, 28 Nov 2017 13:07:32 GMT

Didn't this do the trick? qlik4asif

Re: Restricting bars with selection

qlik4asif — Tue, 28 Nov 2017 13:14:38 GMT

No Omar,