commentary on qlikview file same as excel

[amita1621]

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.

[Gysbert_Wassenaar]

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.

[amita1621]

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?

[amita1621]

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

[Anil_Babu_Samineni]

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

[Gysbert_Wassenaar]

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

[amita1621]

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?

[Gysbert_Wassenaar]

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) )

[amita1621]

Thanks so much.

It works as a great help