topic Re: Problem with SetAnalysis - Quarter in QlikView

Problem with SetAnalysis - Quarter

Thu, 25 Sep 2014 14:12:15 GMT

I need a help for a problem with Set Analysis.

This is my problem:

I would like to build a Pivot table with the Set Analysis where in rows I have the location and in the columns

I have sold in the last quarter, the quarter before last in the second column, and so on.

My idea, as you can see from table 3 of the attached file, is to consider

the maximum value of the data then take the first day of the month and build me a value similar

to the variable "QuarterYear" script loading:

Sum ({<QuarterYear = {"$ (= (Year (monthstart (max (SoldDate), 0)) & '/' & Ceil (Month (monthstart (max (SoldDate), 0)) / 3)))"}> Sold})

For the second quarter of the previous column I would use a formula like this:

Sum ({<QuarterYear = {"$ (= (Year (monthstart (max (SoldDate) - 3)) & '/' & Ceil (Month (monthstart (max (SoldDate) - 3)) / 3)))" }>} Sold)

where to find the previous quarter subtract three months from the current month.

Can anyone help me?

Thank All

rubenmarin — Thu, 25 Sep 2014 14:35:23 GMT

Hi Marino, I removed the extra espaces in set analysis and it worked.

From previous months you'll need tho put the '-3' outside the monthstart function, so it substract from month, not from max(date)

maxgro — Thu, 25 Sep 2014 14:38:52 GMT

what about a pivot with ?

dimensions

Location

QuarterYear

and expression

Sum( {$ <SoldDate={">=$(=AddMonths(MonthStart(max(SoldDate)),-11))"}> } Sold)

change -11 with the number of months you need

Thu, 25 Sep 2014 14:48:52 GMT

Thank Ruben.

Very Nice.

But I do not understand what is the difference with my solution.

Please you tell me the differences?

many thanks

rubenmarin — Thu, 25 Sep 2014 14:56:41 GMT

You had a blank space between '$' and '(', also before closing the double quotes was another empty space:

Sum( {<QuarterYear={"$(=(Year(monthstart(max(SoldDate),0)) &'/' &Ceil(Month(monthstart(max(SoldDate),0))/3)))"}> } Sold)

Removing those it worked.

Glad to help.