Compare values in Rolling Quarter

[paolojolly]

Hi, I'm not very experienced in QlikView and I need help to create a pivot table like this

  

from a excel file as one attached

Can someone help me?

thanks

[pratap6699]

while loading time u have to create quarter field based on datefield;;;'Q' & ceil(datefield/3) as quarter,

in chart dim-->quarter

expression-->sum({<quarter={'$(=quarter-1)'}>}value)

[swuehl · Accepted solution]

I don't this will work as required:

a) there is no definition of quarter as required (rolling 3 months back from yesterday)

b) accessing the dimension value of quarter in the set modifier will not work, a set expression is evaluated in global context

Try like this:

DATA:
LOAD Date, 
     Value
FROM

(biff, embedded labels, table is [Foglio1$]);


Extrema:
LOAD
min(Date) as Min,
max(Date) as Max
Resident DATA;


Let vMinDate = Peek('Min',0,'Extrema');
Let vMaxDate = Peek('Max',0,'Extrema');
Let vNumIntervals = ceil((today() - vMinDate) / 30.5);


drop table Extrema;


Intervals:
JOIN (DATA) IntervalMatch (Date) LOAD  
AddMonths(today(),-(recno()*3)) as StartDate,
AddMonths((today()-1),-(recno()-1)*3) as EndDate
AutoGenerate $(vNumIntervals);


RESULT:
LOAD *, 
  dual(Date(StartDate) & ' - ' & Date(EndDate),StartDate) as RollingQuarter
Resident DATA;


drop table DATA;

And in the UI a chart with dimension  RollingQuarter and expressions like

=sum(Value)

=above(sum(Value),4)

=column(1) / column(2)

[prashantbaste]

Please check attached QVW & let me know if your query is resolved.

[paolojolly]

It is exactly what I needed

Thanks

[paolojolly]

This is another interesting solution, but for my needs I use the Swuehl 's solution

Thanks