Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi, try this..
=Sum ({<YEAR_ACCOUNTING={"$(vReference_Year)"},PERIOD={"<=$(vReference_Period)"}>}[ACCOUNTING AMOUNT])
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
hi,
see attached. i think this is what you're ! asking but correct me if i am wrong.
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
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])
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
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.