topic Adding a Field in a Group(Cyclic) to display latest two weeks in QlikView

Adding a Field in a Group(Cyclic) to display latest two weeks

Wed, 13 Apr 2011 15:49:02 GMT

In one of my pivot table I have a group called as Time. Which contains fields such as =Week(date),=Year(date),=WeekName(date) and =MonthName(date). With that I want to one more fields which calculates only 2 weeks of data. It should show latest 2 weeks date only.

I tried something like =Date(Max(date)-14) but it didn't work.

Can some one help me out on this please.

Thanks

Rikeb

Adding a Field in a Group(Cyclic) to display latest two weeks

vgutkovsky — Wed, 13 Apr 2011 16:50:25 GMT

Rikeb,

When I've developed this functionality in my own applications, I've left the dimension alone and just modified the expression. Just add a valuelist('Last 2 Weeks') to your cycle group. Then, in your expression, use GetCurrentField to check if you're on the "Last 2 Weeks" dimension and, if so, use set analysis to restrict your dates.

Regards,

Adding a Field in a Group(Cyclic) to display latest two weeks

deepakk — Wed, 13 Apr 2011 17:38:33 GMT

hi Attitude,

Try out this application.

Adding a Field in a Group(Cyclic) to display latest two weeks

Wed, 13 Apr 2011 17:55:16 GMT

Hi Deepak

Thanks for your reply. There is nothing in the application. Please attach the correct file.

Adding a Field in a Group(Cyclic) to display latest two weeks

Wed, 13 Apr 2011 17:58:34 GMT


Vlad Gutkovsky wrote:
When I've developed this functionality in my own applications, I've left the dimension alone and just modified the expression. Just add a valuelist('Last 2 Weeks') to your cycle group. Then, in your expression, use GetCurrentField to check if you're on the "Last 2 Weeks" dimension and, if so, use set analysis to restrict your dates. <div></div>

Hi Vlad

Can you please attach the sample application. I am not able to understand what is meant my value list. If you can attach any sample application it will be easy for my understanding.

Adding a Field in a Group(Cyclic) to display latest two weeks

vgutkovsky — Wed, 13 Apr 2011 18:03:55 GMT

I don't have a sample application handy. Look in the Help file for valuelist (it's basically just a way of adding a hardcoded dimension).

Regards,

Adding a Field in a Group(Cyclic) to display latest two weeks

Wed, 13 Apr 2011 18:27:06 GMT

Hi Vlad

I have attached the sample application for your understanding. Can you please work on it.

Hope others also can have a look at it. As I have attached the sample application it will be easy for you.

Adding a Field in a Group(Cyclic) to display latest two weeks

deepakk — Thu, 14 Apr 2011 05:11:08 GMT

Sorry.

I modify the your application. PFA

Adding a Field in a Group(Cyclic) to display latest two weeks

Thu, 14 Apr 2011 06:09:25 GMT

Hi Deepak

Thanks a ton! You have done a excellent job. Hats of to you...

By the way I want one more thing from you when you are free. I want RED color in the background for those columns(date) which falls on weekends(Saturday & Sunday). Could you please try that in the application attached by me previously.

Adding a Field in a Group(Cyclic) to display latest two weeks

deepakk — Thu, 14 Apr 2011 06:32:44 GMT

Happy to Help .

PFA for the sunday part.

Adding a Field in a Group(Cyclic) to display latest two weeks

Thu, 14 Apr 2011 06:48:15 GMT


Deepak Kurup wrote:
Happy to Help .
 PFA for the sunday part. <div></div>

Great job again! Could you please tell me how you managed to that please. By the way I have edited the above post of mine. I want the background color in RED for weekends(Saturday and Sunday). I want the background color in RED in the inside the chart not in the header part. If not possible to bring that inside the chart then please do let me know.

But would like to know how you managed to get the background color in RED for the header. I couldn't find that anywhere!

Adding a Field in a Group(Cyclic) to display latest two weeks

deepakk — Thu, 14 Apr 2011 07:00:58 GMT

hi Attitude,

you can do it on values also. Just go to dimension and next to the cyclic group there is plus sign, reduce it , You will see the background. Use the same expression in the Expression background and it will dsiplay red over there too.

Adding a Field in a Group(Cyclic) to display latest two weeks

Thu, 14 Apr 2011 07:39:39 GMT


Deepak Kurup wrote:
you can do it on values also. Just go to dimension and next to the cyclic group there is plus sign, reduce it , You will see the background. Use the same expression in the Expression background and it will dsiplay red over there too. <div></div>

Great Thinking Deepak! Looks like there is lot to learn from you.

But I am sorry if my questions are too silly. I want the background color to be red for weekends so I used the below code as per your suggestions. The problem is here is that Sun-Friday are getting the BG color as red instead of only Sat and Sun. Also when I change the group the BG color of all the columns are in red for all the fields of the group. Please suggest something for me.

I want the BG color to be in RED for sat and sun only when field selected in the group is 'Two Weeks' But whereas when I used the below code BG color is in red for all the fields of the group.

Hope anything is not understandable please do let me know. Also do it only when you are free. As you must be busy with your work I don't want to disturb you.


=if(weekday(date) ='Sat','Sun',Rgb(255,0,0))

Adding a Field in a Group(Cyclic) to display latest two weeks

Thu, 14 Apr 2011 08:27:28 GMT

Hi Deepak

I tried something like below and it does the trick. Many thanks for your effort and time. Sorry if my above post are confusing and not understandable! 🙂



=if(weekday(date) ='Sat' or weekday(date) ='Sun',Rgb(255,0,0))

Adding a Field in a Group(Cyclic) to display latest two weeks

Thu, 14 Apr 2011 10:33:29 GMT

Hi Deepak

With the application which you provided the solution above:

Go To: Properties --> Presentation --> Tick: Vertical Text on Column Lables

Then select "Two Weeks" in the chart. See the width of the header! It is too large. But when you select other fields in the group it is smaller. Looks like this is because of the calculation we did for "Two Weeks" in the groups.

Is there is anyway to over come this please?

Adding a Field in a Group(Cyclic) to display latest two weeks

Thu, 14 Apr 2011 10:51:32 GMT

Hi Deepak

I have managed to solve this problem by following the below steps.

Go To: Properties --> Dimensions --> Select Time in the used dimensions --> Give some name in the box below the Label(tick box)

Adding a Field in a Group(Cyclic) to display latest two weeks

Thu, 14 Apr 2011 11:17:01 GMT

Hi Deepak

Solutions provided by you is not working when I select any dates(year). It is displaying the repeated columns in the chart.

Whereas in my original application even without selecting any dates it is not working. It showing blank header in the end of the columns.

when you find time please look into it. I will keep you updated if I can give any more details.

Adding a Field in a Group(Cyclic) to display latest two weeks

Thu, 14 Apr 2011 12:22:37 GMT

I am sorry If it is disturbing for you. I have tried the solution that you have given in my application but it didn't seems to be working. As it is little urgent for me requesting you to give some solution when you have free time.

Problem is that I have added 'Two Weeks' field in the group and the data contains max(date)=23/12/2011. But when I use the two weeks field in the group it is displaying only 09/12/2100 - 22/12/2011, it is not displaying 23/12/2011. It is shown as blank(Look into the screen shot).

Also I tried the solution which you have given for sunday. But that also didn't seems to be working.

Have attached the sample application for your understanding. If anything is not understandable please let me know.

Adding a Field in a Group(Cyclic) to display latest two weeks

deepakk — Thu, 14 Apr 2011 12:23:29 GMT

hi Attitude,

Can you attach the latest application with describing the problem.. I will check it and let u know asap

Adding a Field in a Group(Cyclic) to display latest two weeks

deepakk — Thu, 14 Apr 2011 12:32:26 GMT

hi Attitude,

In DImensionn , below there will supress null value. please tick onn that.

Chekc your condition date <= vMaxDate . Equal to sign should be there. or check in VMaxDate what value you are getting.