topic Re: If Colour Statement (Set Expression within a Pivot Table) in QlikView

If Colour Statement (Set Expression within a Pivot Table)

Mon, 27 Apr 2015 10:04:39 GMT

Hi All,

I cannot seem to get my if colour statement working quite the way I want it to so would appreciate your help!

if(sum({$<Week_Commencing = {"<=$(vMaxDate), >=$(=vPriorWeek)"}, Dim1 = {'Test123'}>} [Amount]) <=1, lightred(),)

I am trying to flag the Amount if it less than 2 for a particular 'Dim1', but not the others as this calculation is not relevant for them.

Thanks in advance

Re: If Colour Statement (Set Expression within a Pivot Table)

Gysbert_Wassenaar — Mon, 27 Apr 2015 10:18:12 GMT

Week_Commencing = {"<=$(vMaxDate), >=$(=vPriorWeek)"}

This is an OR filter. It will create a set of values that are (smaller or equal to vMaxDate) OR (larger or equal to vPriorWeek).

If you want an AND filter then remove the comma: Week_Commencing = {">=$(=vPriorWeek)<=$(vMaxDate)"}

Re: If Colour Statement (Set Expression within a Pivot Table)

Mon, 27 Apr 2015 13:00:11 GMT

I did not make my self clear enough the sum works as I require it to. It is the if <=1 colour statement that does not work in the way that I require it to.

I only want the background of the cell to change for the 'Dim1 = {'Test123'} ' part of the expression

Thanks

Re: If Colour Statement (Set Expression within a Pivot Table)

Gysbert_Wassenaar — Mon, 27 Apr 2015 13:50:49 GMT

I don't understand yet. What are the conditions for when the background color should change? If only Dim1='Test123' is the condition then the expression would be if(Dim1='Test123', lightred()).

Re: If Colour Statement (Set Expression within a Pivot Table)

Mon, 27 Apr 2015 15:20:52 GMT

So the expression I am currently using which is working is:

sum({$<Week_Commencing = {"<=$(vMaxDate), >=$(=vPriorWeek)"}>} [Amount])

I want this to change colour if Dim1 = {'Test123'} <=1

Thanks

Re: If Colour Statement (Set Expression within a Pivot Table)

Gysbert_Wassenaar — Mon, 27 Apr 2015 16:58:07 GMT

if Dim1 = {'Test123'} <=1

I don't understand this at all. I'm afraid things are getting less clear instead of more.