topic Re: Change in Month data in QlikView

Change in Month data

Wed, 25 Jan 2017 19:31:07 GMT

I have the following formula that is not working. I am looking in the +/- change Month over Month. I am only getting the same value as the current month with the below formula.

=Num(Count(Distinct{<[Monthly Issue Severity]={High},[Monthly Load Date] = {'$(=Max([Monthly Load Date]))'}>} [Monthly Issue ID])-Count(Distinct{<[Monthly Issue Severity]={High},[Monthly Load Date] = {'$(=Max([Monthly Load Date]) - 1)'}>} [Monthly Issue ID]),'0')

Any assistance would be appreciated!

Re: Change in Month data

vishsaggi — Wed, 25 Jan 2017 19:38:21 GMT

Put single quotes in

{<[Monthly Issue Severity]={'High'}

and Try.

Re: Change in Month data

Wed, 25 Jan 2017 19:45:54 GMT

Thank you, I actually did have those in the calculation, but they didn't transfer over.

=Num(Count(Distinct{<[Monthly Issue Severity]={'High'},[Monthly Load Date] = {'$(=Max([Monthly Load Date]))'}>} [Monthly Issue ID])-Count(Distinct{<[Monthly Issue Severity]={'High'},[Monthly Load Date] = {'$(=Max([Monthly Load Date]) - 1)'}>} [Monthly Issue ID]),'0')

I still have the same amount month over month, and not the Change, I am looking for.

Re: Change in Month data

vishsaggi — Wed, 25 Jan 2017 19:53:40 GMT

What do you get if you just use one single count expression in a text object? Did you try?

Just single expression i mean..

Count(Distinct{<[Monthly Issue Severity]={'High'},[Monthly Load Date] = {'$(=Max([Monthly Load Date]))'}>} [Monthly Issue ID])

Can you share a sample app you are working on?

Re: Change in Month data

Wed, 25 Jan 2017 21:08:09 GMT

I have that in a different column. That is how I know that the numbers current month and change are the exact same.

For the H column the calculation is:

=Num(count(Distinct{<[Monthly Issue Severity] ={'High'},[Monthly Load Date] = {'$(=Max([Monthly Load Date]))'}>} [Monthly Issue ID]),'##,###')

For the +/- (MoM) column, the calculation is :

As you can see, the results are the exact same, and they should not be. I am looking for the change month over month in the right column.

Re: Change in Month data

vishsaggi — Wed, 25 Jan 2017 22:27:23 GMT

Seems correct to me, but cant test unless i have some sample data. Can you share some sample data please??

Re: Change in Month data

Wed, 25 Jan 2017 23:53:49 GMT

Attached is the data that is being used by my Pivot table.

Re: Change in Month data

vishsaggi — Thu, 26 Jan 2017 02:20:42 GMT

Your excel sheet has only two dates, 1/1/2017 and 12/1/2016, when you do Max(Load Date) - 1 what is the date you are expecting?

Re: Change in Month data

uacg0009 — Thu, 26 Jan 2017 02:44:34 GMT

Hi Silvia,

I think you need to test the date type in the set analysis, because in the set analysis the data type should be the same.

In your example, [Monthly Load Date] = {'$(=Max([Monthly Load Date]) - 1)'} :

I think maybe the [Monthly Load Date] is the date type, but the Max([Monthly Load Date]) - 1, i think it is a number type, you should use date() function to change the it, maybe like date(Max([Monthly Load Date]) - 1), you can try it.

Thanks.

Re: Change in Month data

Thu, 26 Jan 2017 10:40:02 GMT

Hi Vishwarath

the Max Load Date - 1 should return with the data from 12/1/2016. so the change in should be the +/- between 1/1/2017 and 12/1/2016.

Re: Change in Month data

Thu, 26 Jan 2017 10:45:52 GMT

Aiolos, the Load Date is the same field, there are dates that have been imported, and have been set to maintain date status in the load statement.

Load Statement:

Date([Monthly Load Date],'M/D/YYYY') as [Monthly Load Date]

I did try what you mentioned above and there was no change in the +/- calculation, I am still getting the same result as the Monthly Load date calculation.

Re: Change in Month data

vishsaggi — Thu, 26 Jan 2017 12:48:30 GMT

Yes, but your excel sheet has not got any dates between 1/1/2017 and 12/1/2016. So i am not sure if this is what you are looking for. Attached is the sample i worked on, if not send me new updated sheet with some dates between those dates.

Re: Change in Month data

stabben23 — Thu, 26 Jan 2017 13:54:12 GMT

Hi Silvia,

this [Monthly Load Date] = {'$(=Max([Monthly Load Date])) will gives you 2017-01-01 if you have correct dateformat.

This [Monthly Load Date] = {'$(=Max([Monthly Load Date]) - 1)'}>} will NOT gives you 2016-12-01 as you expect.

It could gives you a numeric value as Aiolos says, it could gives you 2017-01-00

Put both Expressions in a straigt tablechart without any label, hover over label in chart and see what you get.

Re: Change in Month data

stabben23 — Thu, 26 Jan 2017 14:04:22 GMT

Try this Expression after the '-' in your expression

Count(Distinct{<[Monthly Issue Severity]={'High'},[Monthly Load Date] = {"$(=max(addmonths([Monthly Load Date],-1))) "}>} [Monthly Issue ID]),

Re: Change in Month data

vishsaggi — Thu, 26 Jan 2017 14:23:19 GMT

Sorry misunderstood i believe, you were looking for Month on Month right so You want to see the 1/1/2017 counts and your date-1 is like 1 month behind so it will be 12/1/2016. Just follow what Staffan mentioned to use Addmonths and try?

Re: Change in Month data

vishsaggi — Thu, 26 Jan 2017 14:52:40 GMT

Adding to what Staffan mentioned use below to get the difference. I used this when loading data from excel in the load statement

LOAD Date(Num([Monthly Load Date]), 'MM/DD/YYYY') AS [Monthly Load Date], col2, co3

FROM yourexcelsource;

You H column will be

=count({1}Distinct{<[Monthly Issue Severity] ={'High'},

[Monthly Load Date] = {"$(= Max([Monthly Load Date]) )"}>} [Monthly Issue ID])

Your PreviousMonth will be

= Count({1}Distinct{<[Monthly Issue Severity]={'High'},

[Monthly Load Date] = {"$(= Max(Date(AddMonths([Monthly Load Date], -1), 'MM/DD/YYYY')) )"}>} [Monthly Issue ID])

Your difference will be +/- MOM

= Num(Count(Distinct{<[Monthly Issue Severity]={'High'},

[Monthly Load Date] = {"$(=Max([Monthly Load Date]))" }>} [Monthly Issue ID])

Count(Distinct{<[Monthly Issue Severity]={'High'},

[Monthly Load Date] = {"$(= Max(Date(AddMonths([Monthly Load Date], -1), 'MM/DD/YYYY')) )"}>} [Monthly Issue ID]), '0')

Re: Change in Month data

Thu, 26 Jan 2017 16:59:03 GMT

I still get the same result, using your formula Staffan.

I should be getting a change of -1. The Monthly Load Date is a date for 12/1 and 1/1, I ensured it in my Load Statement, I am not sure why my formula is not working as it should, by giving me the answer of -1

Re: Change in Month data

Thu, 26 Jan 2017 17:01:59 GMT

For some reason the formula is not calculating the data for 12/1, I used your advice Staffan and for the period of 12/1, I am getting 0 for Total Monthly Issue ID, when I should get 125

Re: Change in Month data

vishsaggi — Thu, 26 Jan 2017 17:03:26 GMT

Did you try my expressions ? It is giving me -1 ?

Attached.

Re: Change in Month data

Thu, 26 Jan 2017 17:13:31 GMT

You are a life saver!! Thank you so much!