topic Re: replace the hardcoding formula in QlikView

replace the hardcoding formula

bharatkishore — Thu, 27 Dec 2018 12:05:16 GMT

Hi All,

I have written the below formula :

If(Sum(Consumption) <> 0,Only({<MonthYear={">=$(=Date(AddMonths(Max(MonthYear), -96)))<=$(=Date(Max(MonthYear)))"}>} Aggr(

RangeSum(Above(Sum({<MonthYear={">=$(=Date(AddMonths(Max(MonthYear), -96)))<=$(=Date(Max(MonthYear)))"}

>} Consumption), 0, Month(Only({<MonthYear={">=$(=Date(AddMonths(Max(MonthYear), -96)))<=$(=Date(Max(MonthYear)))"}>} Month))))

, cat, (MonthYear, (NUMERIC)))))

But every time i don't want to change that 96(those many months(7 years) i have). Because next month i have to change to 97 then next month 98...

I don't want it to be hard coding.

Can you please help me how it to be automated.

Please let me know if you need anything more.

Thanks,

Bharat

Re: replace the hardcoding formula

sunny_talwar — Thu, 27 Dec 2018 12:08:21 GMT

So it seems that the lower limit is fixed at a certain date? Will it always be fixed there? meaning with each passing month you will keep on increasing the number? 96, 97, 98, .... 110, ...., 120, ....150... etc? or does it every reset?

Re: replace the hardcoding formula

bharatkishore — Thu, 27 Dec 2018 12:15:58 GMT

Thank you sunny bhai for reply.
yes sunny bhai. because i am trying to calculate YTD and this is a monthly refresh.
Since i have 7 years i.e. 96 months i am giving 96 then again next month i will do a refresh then the number will be 97 and the next month it will be 98..

Re: replace the hardcoding formula

sunny_talwar — Thu, 27 Dec 2018 12:23:58 GMT

But next year will the number is 96 + 12 = 108 and then then year after it will be 96 + 24 = 120?

Re: replace the hardcoding formula

bharatkishore — Thu, 27 Dec 2018 12:27:46 GMT

No Sunny Bhai, it will be 96+1 =97 for jan and for feb it will 97+1=98... because it is a monthly refresh.
please let me know if something is not clear..

Re: replace the hardcoding formula

sunny_talwar — Thu, 27 Dec 2018 12:30:33 GMT

Right but what will happen a year from now

Jan2019 - 97

Feb2019 - 98

Mar2019 - 99

Apr2019 - 100

May2019 - 101

Jun2019 - 102

July2019 - 103

Aug2019 - 104

Sep2019 - 105

Oct2019 - 106

Nov2019 - 107

Dec2019 - 108 or 96?

Jan2020 - 109 or 97?

Does my question may sense?

Re: replace the hardcoding formula

bharatkishore — Thu, 27 Dec 2018 12:40:49 GMT

Jan 2020 will be 109 and feb 2020 will be 110 and so on...
Since i need to do YTD the formula what i have written is :
Only({<MonthYear={">=$(=Date(AddMonths(Max(MonthYear), -96))
96 is the number of months i have. Since i have data from jan 2012 to dec 2018 it is 96 months.
So i have given hardcoding which i don't want to do everymonth.
Please let me know if anything is not clear Sunny bhai..

Re: replace the hardcoding formula

sunny_talwar — Thu, 27 Dec 2018 12:43:22 GMT

If that is the case... then why don't you just use this

MonthYear = {">=$(=Date(MakeDate(2012)))<=$(=Date(Max(MonthYear)))"}>}

Because the beginning will always be Jan 2012... then why do you even need to do -96, -97,... and so on?

Re: replace the hardcoding formula

bharatkishore — Thu, 27 Dec 2018 12:57:04 GMT

Thank you Sunny Bhai.. it worked perfectly...One final question
for Date(MakeDate(2012))) is it is possible to give some min(MonthYear).. can we do anything like that..

Re: replace the hardcoding formula

sunny_talwar — Thu, 27 Dec 2018 12:58:26 GMT

I think so... may be just Min({1} MonthYear)

Re: replace the hardcoding formula

sunny_talwar — Thu, 27 Dec 2018 12:59:13 GMT

Or just Min(MonthYear)... not entirely sure what you have and trying to do... so just guessing 🙂

Re: replace the hardcoding formula

bharatkishore — Thu, 27 Dec 2018 13:06:21 GMT

Thank you Sunny Bhai.. this also worked. I have replaced like below:

If(Sum(Consumption) <> 0,Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Aggr(

RangeSum(Above(Sum({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Consumption), 0,

Month(Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Month))))

, (MonthYear, (NUMERIC)))))

The values are fine.. please let me know if i am doing anything wrong in the formula..

Thank you again Sunny Bhai..

Re: replace the hardcoding formula

sunny_talwar — Thu, 27 Dec 2018 13:11:14 GMT

I might be wrong. but may be you just need this

Aggr(RangeSum(Above(Sum({<MonthYear>} Consumption), 0, Month(Only({<MonthYear>} Month)))), (MonthYear, (NUMERIC)))

Re: replace the hardcoding formula

bharatkishore — Thu, 27 Dec 2018 13:19:47 GMT

No Sunny Bhai it didn't work.. but when i have replaced with your earlier suggestions everything worked perfectly....

One final help Sunny Bhai... i have the below formula :

sum(IB)/sum(TOTAL<[Month-Year]> {<cat>} IB)

Numerator i have replaced i.e.

If(Sum(Consumption) <> 0,Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Aggr(
RangeSum(Above(Sum({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} IB), 0,
Month(Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Month))))
, cat, (MonthYear, (NUMERIC)))))

Now i need your help to replace the denominator i.e. sum(TOTAL<[Month-Year]> {<cat>} IB)

Can you please help me how to replace the denominator..

Re: replace the hardcoding formula

sunny_talwar — Thu, 27 Dec 2018 13:23:12 GMT

Sure, what do you want to replace it with?

Re: replace the hardcoding formula

bharatkishore — Thu, 27 Dec 2018 13:25:05 GMT

I need to replace this sum(TOTAL<[Month-Year]> {<cat>} IB) in the below one:

If(Sum(Consumption) <> 0,Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Aggr(
RangeSum(Above(Sum({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} IB), 0,
Month(Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Month))))
, cat, (MonthYear, (NUMERIC)))))

Re: replace the hardcoding formula

sunny_talwar — Thu, 27 Dec 2018 13:29:39 GMT

I am not sure what that means. Would you be able to share a sample and the output you expect to see from it?

Re: replace the hardcoding formula

bharatkishore — Thu, 27 Dec 2018 14:22:54 GMT

Soory Sunny Bhai for late reply..

PFA file.. Here the actual formula is sum(IB)/sum(TOTAL<MonthYear> {<cat>} IB)

For numerator(sum(IB)) it should be replaced by which i have done :

If(Sum(Consumption) <> 0,Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Aggr(

RangeSum(Above(Sum({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} IB), 0,

Month(Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Month))))

, cat, (MonthYear, (NUMERIC)))))

Now for

sum(TOTAL<MonthYear> {<cat>} IB)

i need to put in below formula

If(Sum(Consumption) <> 0,Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Aggr(

RangeSum(Above(Sum({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} IB), 0,

Month(Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Month))))

, cat, (MonthYear, (NUMERIC)))))

Please let me know if you need anything more..

Re: replace the hardcoding formula

bharatkishore — Thu, 27 Dec 2018 14:23:18 GMT

Soory Sunny Bhai for late reply..

PFA file.. Here the actual formula is sum(IB)/sum(TOTAL<MonthYear> {<cat>} IB)

For numerator(sum(IB)) it should be replaced by which i have done :

If(Sum(Consumption) <> 0,Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Aggr(

RangeSum(Above(Sum({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} IB), 0,

Month(Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Month))))

, cat, (MonthYear, (NUMERIC)))))

Now for

sum(TOTAL<MonthYear> {<cat>} IB)

i need to put in below formula

If(Sum(Consumption) <> 0,Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Aggr(

RangeSum(Above(Sum({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} IB), 0,

Month(Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Month))))

, cat, (MonthYear, (NUMERIC)))))

Please let me know if you need anything more..

Re: replace the hardcoding formula

sunny_talwar — Thu, 27 Dec 2018 14:33:13 GMT

Something is not right... why are we seeing the chart go up and down... is this chart showing correctly?

Shouldn't we be seeing a chart where each bar should be higher than the previous month's bar?