topic Re: Set Analysis Help in QlikView

Set Analysis Help

Tue, 23 Feb 2016 17:07:14 GMT

=Sum({$<Quarter={'Q4'},{Book1})> - sum({$<Quarter={'Q3'},{Book1} >} grossamount * 4)

Hi All,

Can someone please guide me towards the correct way of doing this SET expression. I have created a Bookmark called Book1 for the accounts needed for revenue calculation. I want to find the difference of income between the two quarters and times it by 4.

Requirements: Difference of income (book1 represents income accounts) between Quarter 4 and Quarter 3 times it by four.

Please shade some light.

Thanks in advance,

Re: Set Analysis Help

sunny_talwar — Tue, 23 Feb 2016 18:42:32 GMT

May be this:

=(Sum({[Book1]<Quarter={'Q4'}>} grossamount) - Sum({[Book1]<Quarter={'Q3'}>} grossamount)) * 4

Re: Set Analysis Help

Tue, 23 Feb 2016 19:32:15 GMT

Hi Sunny,

The formula works great, thanks. The formula is stagnant and solid and doesn't change if I make selections. Is there anyone I can automate this formula and make it dynamic so whenever the end user clicks on a year and the quarter the formula changes.

I want to have this:

=money(Sum({[Book1]<Quarter={'Selected Quarter'}>} grossamount) - Sum({[Book1]<Quarter={'Previous Quarter}>} grossamount)) * 4

I have also tried converting the textbox to money but isn't working.

Thanks in Advance,

Re: Set Analysis Help

sunny_talwar — Tue, 23 Feb 2016 19:41:08 GMT

Not 100% sure, but try this:

=money(Sum({[Book1]<Quarter={'Selected Quarter'}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} grossamount) - Sum({[Book1]<Quarter={'Previous Quarter}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} grossamount)) * 4

Re: Set Analysis Help

Tue, 23 Feb 2016 19:58:59 GMT

Hi Sunny,

I know it's difficult to get the formula right without having the data to practice with, I thank you for your hardwork. This formula might be it but the 'Selected Quarter' and "Previous Quarter' aren't actual fields in my dataset. Selected Quarter can be any quarter chosen by the end user after he/she have chosen the year.

Example: Let's say the user selects 2014 and Third Quarter, the formula should look like this.

=money(Sum({[Book1]<Quarter={'Q3'}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} grossamount) - Sum({[Book1]<Quarter={'Q2'}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} grossamount)) * 4

Once again, thanks Sunny!!!!!!!!!

Re: Set Analysis Help

sunny_talwar — Tue, 23 Feb 2016 20:59:30 GMT

Create another field in the script like this:

LOAD Ceil(Month(Date)/3) as QuarterNum

and then try this expression:

=Money(Sum({[Book1]<QuarterNum={"$(=Max({$}QuarterNum))"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} grossamount) - Sum({[Book1]<QuarterNum={"$(=Max({$}QuarterNum)-1)"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} grossamount)) * 4

Re: Set Analysis Help

Tue, 23 Feb 2016 21:09:14 GMT

Sunny I already have a Quarter field called the DimQuarter and DimYear for Year field. This formula can actually do it but for some odd reason it gives me a zero for all the filters, just zero even though the formula has no mistake whatsoever. I wish I can send you this file but I have my hands shackled.

Re: Set Analysis Help

sunny_talwar — Tue, 23 Feb 2016 21:16:27 GMT

Well the Quarter you have is a text and the one I mentioned above is going to be a number. You cannot find Max(Quarter), but you can find Max(QuarterNum).

LOAD Ceil(Month(Date)/3) as QuarterNum

This will give you 1, 2, 3, 4 instead of Q1, Q2, Q3, Q4

Re: Set Analysis Help

Tue, 23 Feb 2016 21:36:11 GMT

Awesome Sunny it seems to be working. Still I can't get the formula to display the dollar sign. And also I want to divide the whole formula now by the expenses I have outlined the expense accounts in my Bookmark 2 called Book 2 for the previous quarter so would my expression at the bottom work in this case.

=Money(Sum({[Book1]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum))"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.grossamount) -

Sum({[Book1]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum)-1)"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.grossamount)) * 4 /

Sum({[Book2]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum)-1)"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.expenses)

Re: Set Analysis Help

jagan — Wed, 24 Feb 2016 03:28:24 GMT

Hi,

Try this expression.

=Money((Sum({[Book1]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum))"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.grossamount) -

Sum({[Book1]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum)-1)"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.grossamount)) * 4 /

Sum({[Book2]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum)-1)"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.expenses))

Regards,

jagan.

Re: Set Analysis Help

sunny_talwar — Wed, 24 Feb 2016 09:18:56 GMT

To make the dollar sign work in your previous expression, you would do this:

=Money(Sum(({[Book1]<QuarterNum={"$(=Max({$}QuarterNum))"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} grossamount) - Sum({[Book1]<QuarterNum={"$(=Max({$}QuarterNum)-1)"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} grossamount)) * 4)

Basically wrapping Money() function around the whole thing (including the 4)

and for your second request, may be as jagan‌ prescribed. Let us know if that doesn't work.

Re: Set Analysis Help

Wed, 24 Feb 2016 13:30:54 GMT

Hi Guys,

thanks for your help. I just need the last piece of the pie from you. Lets say I want to compare 2016 Q1 to 2015 Q4. Will it be possible with the above Jagan formula? Currently my formula doesn't include this part. It only provides me with the difference of quarters if it's the same year. What I'm humbly requesting is a rolling quarter change where if I click on Q1 2016 it will provide me with the difference of Q1 2016 - Q4 2015.

Thanks in advance!!!!

=Money((Sum({[Book1]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum))"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.grossamount) -

Sum({[Book1]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum)-1)"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.grossamount)) * 4 /

Sum({[Book2]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum)-1)"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.expenses))

Re: Set Analysis Help

sunny_talwar — Wed, 24 Feb 2016 17:42:13 GMT

Try this:

=Money((Sum({[Book1]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum))"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.grossamount) -

Sum({[Book1]<MC.QuarterNum={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, 4, Max({$}MC.QuarterNum)-1))"}, [MC.DimYear] ={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, Max({$}[MC.DimYear]) - 1, Max({$}[MC.DimYear])))"}>} MC.grossamount)) * 4 /

Sum({[Book2]<MC.QuarterNum={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, 4, Max({$}MC.QuarterNum)-1))"}, [MC.DimYear] ={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, Max({$}[MC.DimYear]) - 1, Max({$}[MC.DimYear])))"}>} MC.expenses))

Re: Set Analysis Help

Wed, 24 Feb 2016 18:47:21 GMT

Hi Sunny,

Can you briefly explain to me the formula itself and the reasons for - 1 = 0, 4 and -1 = 0? Does it tell the formula to look for the numbers for the previous quarter even if the quarter might be from another year(e.g. Q4 of 2015)?

Thanks for ALL your help Sunny!!!

Re: Set Analysis Help

sunny_talwar — Wed, 24 Feb 2016 18:51:45 GMT

This is saying if QuarterNum selected is 1 then instead of showing 0, show 4 for the QuarterNum and same logic for the year. If the QuarterNum is 1, we need to go back one year and use the 4th quarter.

Re: Set Analysis Help

Wed, 24 Feb 2016 19:21:31 GMT

Thanks Jagan and thanks a bunch Sunny. Without your help I would have not been able to tackle this formula.

Thanks again!!!!!!!!