topic Re: commentary on qlikview file same as excel in QlikView

commentary on qlikview file same as excel

amita1621 — Fri, 09 Feb 2018 06:10:35 GMT

Hi All,

I have an excel dashboard where comments are updated using a separate file using vlookup and the file has period(Qtr and Year) with other columns which has values that are comments.

I want to replicate same in qlikview and want an auto matic update of comments in textbox .

I am attaching the screnshots for more clarity.

Re: commentary on qlikview file same as excel

Gysbert_Wassenaar — Fri, 09 Feb 2018 10:38:10 GMT

Looks straightforward.

Load the excel file with the comments.
Create a listbox for the period field.
Add a text box with as expression =Only([Case Reserve Comment])
Select a period in the listbox.
Reload the qlikview document when the excel file is updated.

Re: commentary on qlikview file same as excel

amita1621 — Fri, 09 Feb 2018 12:37:10 GMT

Thanks Gysbert.

This works fine.

But if there is commentary of 3Q2017 and other 4Q2017 then how that listbox can be used.

As 5 comments are of same Qtr and one is of other QTr.

is there any way to do this?

Re: commentary on qlikview file same as excel

amita1621 — Fri, 09 Feb 2018 12:37:52 GMT

I tried this =if(Period='3Q 2017',Only([Top 10 Losses]),Null())

Re: commentary on qlikview file same as excel

Anil_Babu_Samineni — Fri, 09 Feb 2018 13:57:16 GMT

With Only() function it returns only values in row level. Instead, May be use Aggregate functions around?

Re: commentary on qlikview file same as excel

Gysbert_Wassenaar — Fri, 09 Feb 2018 14:12:16 GMT

Use a table box instead of a text box for the comments. Select the periods as necessary in the period listbox.

Re: commentary on qlikview file same as excel

amita1621 — Mon, 12 Feb 2018 06:19:12 GMT

i can select one period value at a time, and i want to provide one less value to other textbox.

Like if 4q 2017 is selected so some should have 3q 2017 values also.how is that possible?

Re: commentary on qlikview file same as excel

Gysbert_Wassenaar — Mon, 12 Feb 2018 17:57:18 GMT

Yes, that's possible with set analysis expressions. But you'll need a numeric quarter-year field. Qlikview can't tell from a string value like 'q4 2017' that the value preceding it should be 'q3 2017'. You could create such a field in the script:

=num(MakeDate(Right([Period,4), 3*Mid([Period],2,1)-2)) as PeriodStart

Then you can use expressions like

selected period: concat( {<Period=,PeriodStart={$(=max(PeriodStart))}>} [Case Reserve Comment], chr(10) )

previous period: concat( {<Period=,PeriodStart={$(=num(QuarterStart(max(PeriodStart),-1)))}>} [Case Reserve Comment], chr(10) )

Re: commentary on qlikview file same as excel

amita1621 — Tue, 13 Feb 2018 10:23:55 GMT

Thanks so much.

It works as a great help