topic Re: Line Chart with Month Dimension Future Values in QlikView

Line Chart with Month Dimension Future Values

rammarthi — Sat, 16 Nov 2024 04:49:13 GMT

Hi All,

I have a line chart with two expressions one for current year and the other for previous year with Month as dimension.

I want to show it in such a way that the expression should show the months only till latest month. i.e., if today's month is Nov 2018, it should show all the months of 2017 and Jan to Nov of 2018 and should not show December value as zero.

If I am activating suppress null values, all the zeroes/ months not having data between Nov 2018 and Jan 2018 are also disappearing. But I only want to suppress future values whose data ain't there not past values.

When I am suppress null all the April, May and Dec are vanishing. I dont want April and May with Zeros disappear, I only want Dec to disappear as data is only till Nov.

*Note: We can see only 2018 data as previous year (2017) data is not there yet.

Thanks in Advance

Ram

Re: Line Chart with Month Dimension Future Values

zzyjordan — Wed, 09 Jan 2019 06:04:47 GMT

Hi, Rammarthi
Can you provide a sample data and your two expressions so that it will be easier to provide a solution?
ZZ

Re: Line Chart with Month Dimension Future Values

joseph_morales — Wed, 09 Jan 2019 06:11:54 GMT

Hi rammarthi,

Could you indicate what expression are you using in the graph?

regards

Re: Line Chart with Month Dimension Future Values

anushree1 — Wed, 09 Jan 2019 06:18:05 GMT

Hi,

I believe the issue is because there is a missing or zero value for a month, so when you visualize it on a chart there is no way a line can be drawn here for a row with missing or zero value so its better you see how the data looks like when you have the same dimension and expression is depicted in a straight table so that you get a fair idea on the data , also if you could provide the sample data , it would help in guiding further

Re: Line Chart with Month Dimension Future Values

uacg0009 — Wed, 09 Jan 2019 07:23:54 GMT

Hi Rammarthi,

I created some sample values for testing my expression, I think below is what you want:

Aiolos Zhao

Re: Line Chart with Month Dimension Future Values

rammarthi — Wed, 09 Jan 2019 07:24:46 GMT

The expression for each of the years is something like this:

=Sum({<Filter1={1},Filter2={2},Sales_Dt={'>=$(=$(vCurrYrStDt))<=$(=$(vCurrYrEDt))'}>}Sales) for Current Year

=Sum({<Filter1={1},Filter2={2},Sales_Dt={'>=$(=$(vPrevYrStDt))<=$(=$(vPrevYrEDt))'}>}Sales) for Previous Year

Data Sample Attached for reference.

Again we need line chart to show zeros for April and May. And don't show the Dec 2018 at all assuming 2017 all the data is present.

Regards

Ram Marthi

Re: Line Chart with Month Dimension Future Values

rammarthi — Wed, 09 Jan 2019 07:27:51 GMT

What ever you are showing for April is good.

Lets assume today's month is Oct. So future Nov and Dec are also showing zeros they shouldn't be shown.

I just attached data sample for 2018 data please check.

Re: Line Chart with Month Dimension Future Values

anushree1 — Wed, 09 Jan 2019 08:00:15 GMT

Hi,

The sample Data provided does'nt have an entry for Months 4 and 5 which is April and May Months , also it lacks data for 2017 , so I have tweaked the Excel to have all these information included and created the chart, please check if it works.

So, all you need is to get the complete data say if there is no sales in any month the data must have an entry with a zero value for that month.In case the data cannot be prepared that way we will need to make changes in the Script to include the missing data with zero value in qlikview itself, as in the current state qlikview will otherwise not know that a value is missing and something needs to be done.

Hope this helps

Re: Line Chart with Month Dimension Future Values

anushree1 — Wed, 09 Jan 2019 08:01:24 GMT

I probably missed the attachment

Re: Line Chart with Month Dimension Future Values

uacg0009 — Wed, 09 Jan 2019 09:40:15 GMT

Hi,
I'm not sure whether you see my qvw and my data,
I think the expression is what you want, in the chart, you can see 0 value in last year,
and no value after today's month.
You can try to use it in your example,
if not correct, please tell me.
Aiolos Zhao

Re: Line Chart with Month Dimension Future Values

anushree1 — Wed, 09 Jan 2019 10:14:45 GMT

So does that mean considering its Oct 2018 you do not want to see data for Nov and Dec for both the Years 2017 and 2018.

If That is the case , please check the attached file . I have here hardcoded the Month as Oct and written a calculated Dimension with Suppress when value is Null option checked in Dimension Tab to get the desired result.

Hope this helps, I only see the Excel as an attachment in your post

Re: Line Chart with Month Dimension Future Values

rammarthi — Wed, 09 Jan 2019 10:26:51 GMT

Use this data in your qvw, you will find it wrong. Both the April, May and Dec should be in current year only not in two years. Data for yester years all the months is there and not zero.

Re: Line Chart with Month Dimension Future Values

anushree1 — Wed, 09 Jan 2019 10:56:19 GMT

Hi,

I am not sure if we have got the problem right here, X axis is going to be a constant we cant have Apr May and Dec appear for 1 measure and not appear for the other.

Re: Line Chart with Month Dimension Future Values

rammarthi — Wed, 23 Jan 2019 18:16:09 GMT

There is a way to achieve this, one should use the if condition with comparison of latest month end date (vLtstMnthEDt) defined as variable in front end(vMaxSalesDT) and maximum date available in backend. And use the following as expression:

=If($(vLtstMnthEDt)<=$(vMaxSalesDT), aggregation expression, null())

And suppress nulls in the expression presentation. This works.