topic Re: Fixed expression in text object in QlikView

Fixed expression in text object

Tue, 06 Sep 2016 16:29:54 GMT

Hi everyone,

I could need some help with fixing an expression in a text object so that I can display a certain calculated value no matter what selections are made.

Here's the problem:

I have a text object with the following text and expression:

='Bankdaten ' & Count (Distinct( if( [SAP Tabelle]='LFBK - Lieferantenstamm (Bankverbindungen)', Änderungsbelegnr )))

Now, I would like to fix this expression, so that the calculated value is shown even if another selection is made.

At the same time, this text box is only supposed to pop up when a certain button is pressed.

Therefore I already created a variable (vBankdatenGesamt), which is set to 1 by clicking the button.

So, I came up with an expression like:

=if(vBankdatenGesamt = '1', 'Bankdaten Gesamt ' & Count ( Distinct( if( [SAP Tabelle]={'LFBK - Lieferantenstamm (Bankverbindungen)', Änderungsbelegnr))))

But I still need to fix this one.

Any help is appreciated!

Kind regards,

Daniela

Re: Fixed expression in text object

Jason_Michaelides — Tue, 06 Sep 2016 16:55:37 GMT

Hi Daniela,

In the text box expression definition put this:

Count({1<[SAP Tabelle]={'LFBK - Lieferantenstamm (Bankverbindungen)'}>} DISTINCT Änderungsbelegnr)

This will ignore ALL current selections and make a selection in Änderungsbelegnr. If you only want to replace any selections in Änderungsbelegnr, just remove the '1' at the start of the set analysis.

To only show the text box when your button is pressed, put this in the "Conditional" box in the Layout tab of the text box properties:

vBankdatenGesamt = 1

Hope this helps,

Jason

Re: Fixed expression in text object

Wed, 07 Sep 2016 06:32:50 GMT

Hey Jason,

many thanks. This works perfectly.

Is there also an option to make it flexible again only for certain selections?

Example:

I press my button "Show nr. bank data" and get my overall number due to the fixed expression. Some further buttons with different selections won't influence the number, which is good, but then I would like to change the time frame. Instead of the overall number, the number should be change in respect to selections concerning month and year.

I got a field "year" and a field "month" in my data set.

Thanks in advance.

Regards,

Daniela

Re: Fixed expression in text object

Jason_Michaelides — Wed, 07 Sep 2016 09:21:24 GMT

dmeyersieck‌ if my response has solved your issue please mark it as correct to close the thread.

Thanks,

Jason

Re: Fixed expression in text object

Jason_Michaelides — Wed, 07 Sep 2016 16:00:02 GMT

Sure. This should do it:

Count({1<[SAP Tabelle]={'LFBK - Lieferantenstamm (Bankverbindungen)'}, year=P(), month=P()>} DISTINCT Änderungsbelegnr)

Re: Fixed expression in text object

Thu, 08 Sep 2016 06:37:34 GMT

I guess this is close, but it is not working yet.

The expression starts to be "wrong" with the closing bracket after the first "P". It seems something needs to be put into brackets, or .... I don't know. I already tried to forgo the brackets after the Ps, but this ends in rubbish

Count ({1< [SAP Tabelle]={'LFBK - Lieferantenstamm (Bankverbindungen)'}, Geschäftsjahr=P(), Monat=P() >} DISTINCT Änderungsbelegnr)

Another Suggestion to try?

Thank you!

Re: Fixed expression in text object

Jason_Michaelides — Thu, 08 Sep 2016 16:23:59 GMT

The expression syntax checker is broken after using P(), but it should still work OK. Is the expression not returning anything? Can you post your app?

Re: Fixed expression in text object

Fri, 09 Sep 2016 06:46:46 GMT

I get some values, but it changes now for any selection again, not only for selections in "year" (Geschäftsjahr) and "month" (Monat).

I have attached an extract of the overall file, since this might already do.

The required text object is called "Test Object" and is only displayed if you press the button "Bankdatenänderungen" or one of the below buttons. It should generally have the same value as the text object with "Bankdaten" on the righthand side - at least as long as you only do selections on the year and month field.

The general value to be displayed here is 14 (all changes in bank data for all years in scope).

Selections in year, month (and later also quarter, day and weekday) should lead to new values according to the selection. Pressing the below button "CPD-Kreditoren" should not change the value of 14, but it does.

The same is true for selections in any other list box like "Änderer" or "Geändertes Feld" or ...

I hope we can figure this out.

Regards,

Daniela

Re: Fixed expression in text object

Jason_Michaelides — Sun, 11 Sep 2016 18:09:04 GMT

Oops - sorry! You need to pass specific text strings into the Year and Month set modifiers. Create two variables:

vGeschäftsjahr

='Geschäftsjahr='

& If(GetSelectedCount(Geschäftsjahr)=0

,''

,'{' & Chr(39) & GetFieldSelections(Geschäftsjahr,Chr(39) & ',' & Chr(39),100) & Chr(39) & '}'

)

vMonat

='Monat='

& If(GetSelectedCount(Monat)=0

,''

,'{' & Chr(39) & GetFieldSelections(Monat,Chr(39) & ',' & Chr(39),12) & Chr(39) & '}'

)

Then, your main expression should be:

='Bankdaten Gesamt ' & Count ({1< [SAP Tabelle]={'LFBK - Lieferantenstamm (Bankverbindungen)'},$(vGeschäftsjahr), $(vMonat)>} DISTINCT Änderungsbelegnr)

See attached,

Jason

Re: Fixed expression in text object

Mon, 12 Sep 2016 10:12:21 GMT

Hey Jason,

many thanks for your help. This way, it works exactly the way I want it to.

Two (hopefully) last "small" questions:

1. Is it possible to have a bookmark on a selection combination that is not actually possible?

Example: Field: "SAP Tabelle" with value: "LFBK" and field: "CPD-Kreditoren" with value: "ja".

Since there are no CPD-Kreditoren available for my bank data (represented by selection of SAP Tabelle LFBK) I can not select "ja" in the field CPD-Kreditoren when I first select the LFBK value in the field SAP-Tabelle. However, I would like to show exactly this result, namely that there are no CPD-Kreditroren for bank data. In my text object I would like to see the value "0" then and in my graphic I would like to see something like "no data available".

Maybe there is also a way to work around this. I need this comination for my button "CPD-Kreditoren" beneath the button "Bankdaten".

2. I would like to display my titles dynamically and generally I already figured out how to do this, but again I've got a little trouble with the field CPD-Kreditoren. As long as I have not selected anything in the field "CPD-Kreditoren" I would like to see the title "Bankdaten" with the corresponding value. However, if I dispay my overall bank data (with generally no CPD-Kreditoren available) it already changes the title to "Kreditoren exkl. CPD" since the possible selections in the field "CPD-Kreditoren" are already restricted.

This is the expression I am using so far, but it is not fully right yet.

=if(IsNull([CPD-Kreditoren]), 'Bankdaten ', if([CPD-Kreditoren] ='ja', 'CPD-Kreditoren ', if([CPD-Kreditoren]= 'nein', 'Kreditoren exkl. CPD ', 'Bankdaten '))) & Count (Distinct( if( [SAP Tabelle]='LFBK - Lieferantenstamm (Bankverbindungen)', Änderungsbelegnr )))

See attached.

Any suggestions appreciated.

Regards,

Daniela

Re: Fixed expression in text object

Jason_Michaelides — Mon, 12 Sep 2016 20:10:59 GMT

For your first question you can put anything you like in set analysis:

=Count ({< [SAP Tabelle]={'LFBK - Lieferantenstamm (Bankverbindungen)'}, [CPD-Kreditoren]={'ja'}>} DISTINCT Änderungsbelegnr)

This will simply return 0 if that's the answer.

I don't really understand your second question, but I think maybe look at the functions GetPossibleValues() and GetSelectedValues()

Hope this helps,

Jason

Re: Fixed expression in text object

MarcoWedel — Mon, 12 Sep 2016 23:31:17 GMT

Hallo Daniela,

another solution to only let your text box expression respond to month or year selections might be:

='Bankdaten Gesamt:'&Count({1<[SAP Tabelle]={'LFBK'},Geschäftsjahr=$::Geschäftsjahr,Monat=$::Monat>} DISTINCT Änderungsbelegnr)

vielleicht hilft's

Gruß

Marco

Re: Fixed expression in text object

Tue, 13 Sep 2016 08:11:47 GMT

Thanks a lot! I got it!

I created two new variables (vCPD and vKredexCPD) and integrated them and your above expression into the text field of my text object:

=if(vCPD= '1', 'CPD-Kreditoren ' & Count ({< [SAP Tabelle]={'LFBK - Lieferantenstamm (Bankverbindungen)'}, [CPD-Kreditoren]={'ja'}>} DISTINCT Änderungsbelegnr),
if(vKredexCPD = '1', 'Kreditoren ex CPD ' & Count ({< [SAP Tabelle]={'LFBK - Lieferantenstamm (Bankverbindungen)'}, [CPD-Kreditoren]={'nein'}>} DISTINCT Änderungsbelegnr),
'Bankdaten ' & Count (Distinct( if( [SAP Tabelle]='LFBK - Lieferantenstamm (Bankverbindungen)', Änderungsbelegnr )))))

The variables are set to 1 by pressing the corresponding buttons. This way I managed to display the correct values in my text object with the corresponding titles. And using the same variables I also got my graphic to display "no data" when applicable.

So, both questions answered.

Regards,

Daniela

Re: Fixed expression in text object

Tue, 13 Sep 2016 08:16:51 GMT

Thanks, Marco.

Already integrated the other solution into my qvd-file, but it seems your answer would have done the same job.

Regards,

Daniela

Re: Fixed expression in text object

Thu, 22 Sep 2016 12:00:51 GMT

Hey Jason,

I could need another advice for fixing an expression.

This time I got a text box which is supposed to give me a certain text depending on which year I select.

The below screenshots display the required text to be shown for 2016 and 2013 respectively.

The text itself has some variable factors which keep changing whenever I select a value in any other field.

Currently, when I drill down to certain items of a year, the text keeps changing accordingly. This is what I need to avoid. Especially, the selection in the field "BelegNr." is not allowed to influence the numbers in my text box..

This is my expression:

=Geschäftsjahr & if(Match(Geschäftsjahr,'2013', '2016'), ': ', ' ')
& if( Geschäftsjahr = '2016', Count({1< Geschäftsjahr = {'2016'} >} Distinct BelegNr.) & if(Count({< Geschäftsjahr = {'2016'} >} Distinct BelegNr.)=1, ' Änderung', ' Änderungen')
//& if(Num ([Alter Wert BELEG]) <= Num([Neuer Wert BELEG]),
& if(Count({1< Geschäftsjahr = {'2016'} >} Distinct BelegNr.)>1, '
' & GetPossibleCount(BelegNr.), ' ') & ' von ' & Count({1< Geschäftsjahr = {'2016'} >} Distinct BelegNr.) & ' Änderungen '
& if(Count(Distinct BelegNr.)=1, 'ist', 'sind') & ' nicht ordnungsgemäß. Das Basisdatum wurde von ' & if(Count(Distinct Änderer)=1, 'User ' & Änderer, Count(Distinct Änderer)& ' Usern') & ' in die Zukunft gelegt.' ,
if(Geschäftsjahr = '2013', Count({< Geschäftsjahr = {'2013'} >} Distinct BelegNr.) & if(Count({< Geschäftsjahr = {'2013'} >} Distinct BelegNr.)=1, ' Änderung', ' Änderungen')
& if(Num ([Alter Wert BELEG]) >= Num([Neuer Wert BELEG]), '
Keine Beanstandungen.')) & ' ')

I also attached a sample of my qvd.

Hope you can help me on this one.

A second aspect I would need to solve is that currently all items are counted. However, I would need to have the condition integrated that only items are counted for which [Alter Wert] <= [Neuer Wert] is true, without having those items explicitely selected.

Right now I worked my way around, but the moment more changes are recorded, this might not work anymore.

Example: another items date is changed and this time [Alter Wert] >= [Neuer Wert]. This would be an item not to be counted - leading to the text:

"2016: 7 Änderungen

6 von 7 Änderungen sind nicht ordnungsgemäß. Das Basisdatum ...."

Thanks in advance. Regards,

Daniela

Re: Fixed expression in text object

MarcoWedel — Thu, 22 Sep 2016 18:49:48 GMT

Hi,

please don't post independent questions in one thread, instead open a new one for your new question.

thanks

regards

Marco