Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to find whether there has been any time dimension value selected/filtered in the sheet.
For this I am using GetPossibleCount(Date), but the problem is that the value returned by GetPossibleCount is same when there is no value is selected and when all values of the dimension are selected.
Is there any way to distinguish between 'No' & 'All' Selection ?
That sounds weird, would you be able to post a sample qvf that demonstrates your issue ?
Can you use GetSelectedCount(FieldName) in combination?
I think that you want to use GetSelectedCount instead.
That will return the number of selections made in that filter.
Thanks for your replies.
I might be doing something totally wrong, please correct me if I am wrong.
I need to show last 4 weeks data by default when sheet opens for the first time, and i am trying to achieve the same using the below expression.
if(GetPossibleCount(Date)=Count(Distinct {1}Date),Sum({$<Date={">=$(=date(weekstart(today(),-4),'DD-MM-YYYY'))"}>}Amount),Sum(Amount))
I have attached the qvf for your reference.
Thanks
Would you maybe need to change the expression to this ?
if(Count( distinct Date)=Count(Distinct {1}Date),Sum({$<Date={">=$(=date(weekstart(today(),-4),'DD-MM-YYYY'))"}>}Amount)/Sum({$<Date={">=$(=date(weekstart(today(),-4),'DD-MM-YYYY'))"}>}NumCustomer),Sum(Amount)/Sum(NumCustomer)))
I was referring to the expression for your Average Basket viz
For the expression you pasted in then maybe this:
if(Count( distinct Date)=Count(Distinct {1}Date),Sum({$<Date={">=$(=date(weekstart(today(),-4),'DD-MM-YYYY'))"}>}Amount),Sum(Amount))
That is actually quite cunning what you are doing, assuming I am correct in that what you trying to do is prevent the potential performance hit of calculating and displaying all the data when the qvf is opened.
To make it clearer to your users maybe add a Text & image object with an expression something like this:
= if(Count( distinct Date)=Count(Distinct {1}Date) , 'All Dates selected so only last 4 week displayed' )
Thanks a lot Bill
I still have the problem, 'if' condition does not seem to work in few cases. When time period is selected, the results in the right most visualization containing calendar objects show nothing (For example if I select year 2016).
Ooops!! Looks the chart expression needs a couple of TOTAL 's as well, otherwise it will evaluate at the row level and not the chart level.
if(Count(Distinct TOTAL Date)=Count(Distinct {1} TOTAL Date),
Sum({$<Date={">=$(=date(weekstart(today(),-4),'DD-MM-YYYY'))"}>}Amount)
/
Sum({$<Date={">=$(=date(weekstart(today(),-4),'DD-MM-YYYY'))"}>}NumCustomer),
Sum(Amount)/Sum(NumCustomer)
)
And maybe under Add-ons | Data Handling untick Include zero values