topic Re: edit number format valueloop in QlikView

edit number format valueloop

Anonymous — Mon, 16 Oct 2017 12:16:13 GMT

Hi all,

I want to create a custom dimension with valueloop. This dimension is:

=ValueLoop(today()-7,today(),1)

This gives the 7 last days in number format.

But I need to edit the number format of this dimension so it is a date (DD/MM/YYYY).

I have allready tried =Date(ValueLoop(today()-7,today(),1), 'DD/MM/YYYY') and it doesn't work.

Thanks

Re: edit number format valueloop

effinty2112 — Mon, 16 Oct 2017 12:37:21 GMT

Hi Lluis,

This is a workaround that might work for you:

Add the expression shown in the second column:

=ValueLoop(today()-7,today())	Date(ValueLoop(today()-7,today()))
43017	09/10/2017
43018	10/10/2017
43019	11/10/2017
43020	12/10/2017
43021	13/10/2017
43022	14/10/2017
43023	15/10/2017
43024	16/10/2017

then in the presentation tab hide the first column

Date(ValueLoop(today()-7,today()))
09/10/2017
10/10/2017
11/10/2017
12/10/2017
13/10/2017
14/10/2017
15/10/2017
16/10/2017

Good luck

Andrew

Re: edit number format valueloop

Anil_Babu_Samineni — Mon, 16 Oct 2017 12:38:58 GMT

Are you expecting this in Filter Pane?

Re: edit number format valueloop

Anil_Babu_Samineni — Mon, 16 Oct 2017 14:18:55 GMT

Andrew, Is there anyway in listbox?

Re: edit number format valueloop

effinty2112 — Mon, 16 Oct 2017 14:51:36 GMT

Hi Anil,

You could try this in a listbox:

=Aggr(if(Date > Today()-7 ,Date),Date)

If the Date field belonged to a master calendar with no gaps up to today's date this might give the result we're looking for.

Cheers

Andrew

Re: edit number format valueloop

Anil_Babu_Samineni — Mon, 16 Oct 2017 15:01:30 GMT

If you notice, They are using ValueLoop() with Today() function. That mean i believe we are not having Date field currently, Can this achievable?

Re: edit number format valueloop

effinty2112 — Mon, 16 Oct 2017 15:11:05 GMT

Hi Anil,

I couldn't find a way to use ValueLoop in a calculated dimension to return the text date I'm afraid. If the poster does not have a date field in his DM then I don't know what purpose a listbox returning a date would serve.

Kind regards

Andrew

Re: edit number format valueloop

Anil_Babu_Samineni — Mon, 16 Oct 2017 15:14:11 GMT

I even tried possible ways like Variable and Floor() with Date() format. No luck, Let them reply marcus_sommer‌ stalwar1‌ marcowedel‌ +swuehl

Eagerly waiting for this, If we could do in Qlik

Re: edit number format valueloop

marcus_sommer — Mon, 16 Oct 2017 15:34:54 GMT

AFAIK there is no way to format a valueloop() within a calculated dimension. But in this case such a field could be easily created within the script with something:

load date(today()-7+recno()-1) as SpecialDate autogenerate 7;

- Marcus

Re: edit number format valueloop

MarcoWedel — Mon, 16 Oct 2017 20:55:14 GMT

Hi,

depending on your chart type you might be able to use a continuous dimension axis formatted as date:

hope this helps

regards

Marco

Re: edit number format valueloop

Anil_Babu_Samineni — Tue, 17 Oct 2017 00:52:47 GMT

I thought same, But before commit to others just to check from experts.Thanks Marcus

Re: edit number format valueloop

Anil_Babu_Samineni — Tue, 17 Oct 2017 00:54:08 GMT

If it is chart, Then we can make. But, I am thinking from list box? As Andrew already stated we can go with that by using Custom format cell to remove the cell borders After/Before. Thanks Marco