Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

YTD figures based on Selection.

Hello

I want to display the figures for a specific selection on Year and Period

YEAR is a field YEAR_ACCOUNTING, Period is a field called PERIOD in the script.

I have set up 2 variables, Reference_Year = YEAR_ACCOUNTING and Reference_Period = PERIOD

I want to see accumulated accounting amount for Year = Selected year and Period = Selected Period

I have tried the following

Sum ({$<YEAR_ACCOUNTING = {$(vReference_Year)}, PERIOD = {'<= {$(vReference_Period)'} >}[ACCOUNTING AMOUNT])

but I don't get any data then.

I think this is an easy subject to solve.

Could you help there?

Thank you

Luc

7 Replies
hopkinsc
Partner - Specialist III
Partner - Specialist III

Hi, try this..

=Sum ({<YEAR_ACCOUNTING={"$(vReference_Year)"},PERIOD={"<=$(vReference_Period)"}>}[ACCOUNTING AMOUNT])

Not applicable
Author

Hello Chris

Thanks for this answer.

I have tried with " or with ', but i still don't get any data.

I don't really understand what is happening. For me this formula looks fine. if I just put

=Sum ({$}[ACCOUNTING AMOUNT])

then I need to select all periods to get the up to date data. this is work around but I was expecting to select only one period and that it would give me YTD figures.

What do you think?

Thank you

Luc

De : Chris Hopkins <qcwebmaster@qlikview.com>

À : Luc CELESTIN <luc.celestin@yahoo.com>

Envoyé le : Lundi 24 octobre 2016 15h16

Objet : Re: - YTD figures based on Selection.

|

|

YTD figures based on Selection.

reply from Chris Hopkins in Scripting - View the full discussionHi, try this.. =Sum ({<YEAR_ACCOUNTING={"$(vReference_Year)"},PERIOD={"<=$(vReference_Period)"}>}[ACCOUNTING AMOUNT])

Reply to this message by replying to this email, or go to the message on Qlik Community

Start a new discussion in Scripting by email or at Qlik Community

Following YTD figures based on Selection. in these streams: Inbox

© 1993-2016 QlikTech International AB | Copyright & Trademarks | Privacy | Terms of Use | Software EULA

hopkinsc
Partner - Specialist III
Partner - Specialist III

hi,

see attached. i think this is what you're ! asking but correct me if i am wrong.

Not applicable
Author

Hello Chris

This is much better. It works now for Year but I have a problem with Period. Maybe it comes from the structure I have

For me it should not cause issues

but can be

Thank you

Luc

johnw
Champion III
Champion III

I suspect your data model is causing a problem with the expression, yes. You have a YEAR_ACCOUNTING in the GENERAL_LEDGER table. Select that, and you get only matching cost centers and accounts and intervals and from dates and to dates and so on. You basically need to deselect that with set analysis, and probably any calendar or intervalmatch fields that can be selected. I also don't know why we need to use variables here. Something like this:

sum({<YEAR_CALENDAR={'$(=max(YEAR_ACCOUNTING))'},PERIOD={"<=$(=max(PERIOD))"},ACCOUNTING_DATE=,INTERVAL=,[FROM DATE]=,[TO DATE]=>} [ACCOUNTING AMOUNT])

Not applicable
Author

Hello John

Your solution is good. It works perfectly now.

For Variable, I was thinking we should be using it for such things but too be honest, even if I did develop quite some reports in Qlikview, I never received a proper training.

Thanks a lot for your help

Luc

johnw
Champion III
Champion III

I like variables if I'm using the same expression in multiple places, and if I'm pretty sure I'll want all of them to change if I change any of them. That way I only have one spot to maintain it rather than many. I think I've gotten in trouble in the past, though, with nested variables. Something about how QlikView tries to do everything in parallel, and didn't understand that some variables had to be computed before other variables. That was a long time ago, and I'm not sure if it's a problem in current versions.