Need 5 week rolling in my Qlikview Bar chart from max of week and Status must be closed

[sharu055]

Hello All, i hope your doing good. Could you please help .

I have 4 Column, Amount, Date, Week, Status . In which i need Sum of Amount in bar chart where rolling 5 week from Max of week and only status 'Closed' should show in the chart. should not alter for the status is open.

Amount               Date             Week          Status

12000                    Apr 07         18                Closed

26000                    Apr 07            18                Closed

16000                    Apr 014           19               Closed

6000                     Apr 014             19               Open

26000                    Apr 21               20               Open

6000                     Apr 28                21              Closed

46000                    May 4               22               Closed

12000                    May 4              22               Open

36000                    May 11            23              Closed

16000                    May 11            23              Open

6000                       May 18           24             Closed

21000                     May 18          24             Open

6000                       May 25           25             Closed

46000                    May 25            25             Open

[sidhiq91]

@sharu055  Please use the below expression in your measure and that should resolve the issue,

Sum({<Status={'Closed'},Week={">=$(=Max(Week)-5)<=$(=Max(Week))"} >}Amount)

If you still find any issues, please let me know. Otherwise please like and accept it as a solution.

[sharu055]

@sidhiq91  Thank you so much for helping, it works for rolling 5 week and the chart altering if i select Status = open, its not stick for only Status = closed as i need the bar chart only shows the data for status = closed and i need another help in bar chart  axis i need $ Symbol like below, could you please help.

[Anku]

Please try:

Edit > Data > Measure > Number Formatting > Money.

[sidhiq91 · Accepted solution]

@sharu055  Ideally with the expression that I had given you, the value will not change if you select Open status in the filter pane. However now you can use the below expression and that should resolve your issue.

Sum({1<Status={'Closed'},Week={">=$(=Max(Week)-5)<=$(=Max(Week))"} >}Amount)

Also for the dollar sign in the Y axis, please see the below screen shot.

[sharu055]

@sidhiq91 @Anku  Could you please help me for my below Question

Need to highlight the substring in blue color as below example, Please help

I am having the input string as below i need the output  that particular last word must be in blue color as below output please help,

Input

As sweet as Apple

As sweet as Honey

As sweet as Sugar

As sweet as Mango

As sweet as Water

As sweet as Apple

As sweet as Water

As sweet as Grapes

Output:

Please help.

[sidhiq91]

@sharu055 I am not sure if we can highlight any sub string of the sentence. But would be happy to learn from any of our Community members, @MayilVahanan any suggestions here?

[MayilVahanan · Accepted solution]

Hi

You can create like below with split the values and give color to fruit name

LOAD *, SubField(Fruits,' ',-1) as FruitName, 'As sweet as' as Desc INLINE [
Fruits
As sweet as Apple

As sweet as Honey

As sweet as Sugar

As sweet as Mango

As sweet as Water

As sweet as Apple

As sweet as Water

As sweet as Grapes
];

[sidhiq91]

@MayilVahanan  Thanks much. I am not able to view the code using the attached QVW. Could you please post the code here that you used to get the desired output?

[MayilVahanan · Accepted solution]

HI

There is no code.. just alignment..