topic Re: Help required to create complicated chart in QlikView

Help required to create complicated chart

qlikviewforum — Tue, 17 Nov 2015 12:35:50 GMT

I want to create a chart something like below one. The values that you see is the count of TRUE value against the each city for the selected date range(Based on the date range you have more number of month columns dynamically). It should count the number against each city based on the below expressions.

IF(

IF(Limit_Days='l_30',[30 days],

IF(Limit_Days='l_60',[60 days],

IF(Limit_Days='l_90',[90 days],

IF(Limit_Days='TBD',[60 days)

)

)<1

,sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} 1),

sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} 0))

Has anyone created a similar chart like this? Can you please help on the same since it doesn't looks to be straight forward.

Re: Help required to create complicated chart

qlikviewforum — Wed, 18 Nov 2015 10:32:00 GMT

Any help from any one about creating the above chart?

Re: Help required to create complicated chart

settu_periasamy — Wed, 18 Nov 2015 14:15:06 GMT

Hi,

Do you want to create the chart like your screenshot?

Hope you have Month Field. So, you can create the Pivot table, Include the Month Field in your dimension.

Try with your expression (believe it is working)

can you create the sample with dummy data? it would be helps us to resolve your question.

Re: Help required to create complicated chart

qlikviewforum — Thu, 19 Nov 2015 05:43:14 GMT

Yes I want to create a chart similar to one mentioned in the screen shot. We dont have Month field in our DM.We are just creating it using MonthName(Period) for Month Field and dragging it to the top of expression to make it as cross tab. Basically what i want(Under the month) is that count of number of days the value is < 1 in a month(Basically count of TRUE value of a month based on the below expression).

Please let me know if the requirement is not clear yet.

=IF(

IF(maxstring({<$(e_DateRange)>}Limit_Days)='last_30',$(e_Days('l_30')),

IF(maxstring({<$(e_DateRange)>}Limit_Days)='last_60',$(e_Days('l_60')),

IF(maxstring({<$(e_DateRange)>}Limit_Days)='last_90',$(e_Days('l_90')),

IF(maxstring({<$(e_DateRange)>}Limit_Days)='TBD',$(e_Days('l_60')))

)

)<1

,replace(sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} 1),1,'TRUE'),

replace(sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} 0),0,'FALSE')

)

Re: Help required to create complicated chart

settu_periasamy — Thu, 19 Nov 2015 06:15:19 GMT

So, you are using Calculating Dimension. what you are getting now, if you are using the above mentioned Expressions?

Can you post the screen shot?

Try to look on the Pick Match function also..

Re: Help required to create complicated chart

qlikviewforum — Thu, 19 Nov 2015 06:21:45 GMT

Yes we are using the calculated dimension. I am getting 1 and 1 respectively in both the months. Sorry not possible to attach the screen shot now as it has real data and what i mentioned above is just for your understanding.

By the way not able understand how i will make use of Pick and Match function? Could you please explain

Re: Help required to create complicated chart

settu_periasamy — Thu, 19 Nov 2015 06:30:39 GMT

i'm not able to assume what's happened. Someone will help.

Re: Help required to create complicated chart

qlikviewforum — Thu, 19 Nov 2015 06:33:41 GMT

Ok I know it is very difficult to work without any sample data. I wish to attach but due to security reason I am not attaching any.

Re: Help required to create complicated chart

qlikviewforum — Thu, 19 Nov 2015 09:28:59 GMT

Just to wanted to know how to take the count of TRUE values show the same against month after doing cross tab. That would be much helpful.

Re: Help required to create complicated chart

qlikviewforum — Thu, 19 Nov 2015 12:12:52 GMT

Hi All,

Basically I want to count only those days where the values are TRUE. If there is no TRUE value we just need to flag it as 0. Can someone help me out on this? It is little confusing to go ahead with that.

IF(

IF(Limit_Days='l_30',[30 days],

IF(Limit_Days='l_60',[60 days],

IF(Limit_Days='l_90',[90 days],

IF(Limit_Days='TBD',[60 days)

)

)<1

,sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} 1),

sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} 0))

Re: Help required to create complicated chart

qlikviewforum — Fri, 20 Nov 2015 05:14:27 GMT

Any update from anyone on this?

Re: Help required to create complicated chart

qlikviewforum — Fri, 20 Nov 2015 10:12:57 GMT

Some how I have managed to count number of TRUE value for a month using below expression and by adding Month in the dimension. But the issue here is when there is no TRUE value exists it has display 0 but in my case it is not displaying any records. Please help me out on this?

=Sum(Aggr(IF(

IF(maxstring({<$(e_DateRange)>}Limit_Days)='last_30',$(e_Days('l_30')),

IF(maxstring({<$(e_DateRange)>}Limit_Days)='last_60',$(e_Days('l_60')),

IF(maxstring({<$(e_DateRange)>}Limit_Days)='last_90',$(e_Days('l_90')),

IF(maxstring({<$(e_DateRange)>}Limit_Days)='TBD',$(e_Days('l_60')))

)

)<1

,1, 0 ),Period))