Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
Contributor

edit number format valueloop

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

1 Solution

Accepted Solutions
Honored Contributor

Re: edit number format valueloop

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()))
4301709/10/2017
4301810/10/2017
4301911/10/2017
4302012/10/2017
4302113/10/2017
4302214/10/2017
4302315/10/2017
4302416/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

View solution in original post

11 Replies
Honored Contributor

Re: edit number format valueloop

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()))
4301709/10/2017
4301810/10/2017
4301911/10/2017
4302012/10/2017
4302113/10/2017
4302214/10/2017
4302315/10/2017
4302416/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

View solution in original post

Highlighted

Re: edit number format valueloop

Are you expecting this in Filter Pane?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted

Re: edit number format valueloop

Andrew, Is there anyway in listbox?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Honored Contributor

Re: edit number format valueloop

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

Highlighted

Re: edit number format valueloop

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Honored Contributor

Re: edit number format valueloop

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

Highlighted

Re: edit number format valueloop

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

Eagerly waiting for this, If we could do in Qlik

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
MVP & Luminary
MVP & Luminary

Re: edit number format valueloop

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

Highlighted

Re: edit number format valueloop

Hi,

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

QlikCommunity_Thread_278109_Pic1.JPG

QlikCommunity_Thread_278109_Pic2.JPG

QlikCommunity_Thread_278109_Pic3.JPG

hope this helps

regards

Marco