topic Re: Add quarters upon selection in QlikView

Add quarters upon selection

Brip51 — Tue, 23 Dec 2014 15:25:21 GMT

I need to create a chart that when a user selects a fiscal quarter from a selection box, I sum a dollar amount. The chart displays the selected quarter plus the next 3 fiscal quarters. I have fiscal year and fiscal quarter in my calander selections, but am not sure how to create the logic to say-

" sum dollar amount by Selected Q AND the next 3 quarters after the selected Q"

I can attach my QV doc if that helps?

Any ideas or help would be greatly appreciated.

Thanks.

Re: Add quarters upon selection

francoiscave — Tue, 23 Dec 2014 15:52:40 GMT

Hi Brian,

Can you attached your file to have the possibility to solve your issue?

Thanks,

François

Re: Add quarters upon selection

Brip51 — Tue, 23 Dec 2014 16:14:58 GMT

Hi Francois,

I have attached a qvw file.

What I want is that when a user selects a quarter, the bottom table will sum –

(=num(sum({< StageName = {'Closed/Won'}, AccountSalesRegion = {'West', 'Central', 'Federal', 'Mid Atlantic', 'Commercial', 'NE & Canada'}>} Software_Amount__c),'$#,##0') -

the "Software_Amount__c"

AND the "Software_Amount__c" with Close Date for the following 3 quarters after the selected quarter.

Thank you,

Brian

Re: Add quarters upon selection

consenit — Tue, 23 Dec 2014 16:18:25 GMT

Hi there.

See document attached. The trick is to assign a consecutive ID to the quarters so you can do arithmetic in the set analysis formula, something like:

=SUM

(

{$<

QuarterID={$(=ONLY(QuarterID))}>

}

Sales

)

for the selection, and:

=SUM

(

{1<

QuarterID={$(=ONLY(QuarterID)+1)}>

}

Sales

)

for the next quarter, etc.

Kind regards.

Ernesto.

Re: Add quarters upon selection

Brip51 — Tue, 23 Dec 2014 16:32:13 GMT

Hi Ernesto,

That is brilliant. I would have never thought of that.

Thank you!