Another question about getSelectedCount

[#1]

Hello again,

     I think i'm addicted to this function . But i hit the wall again and looking for some help.

I have date picker, which uses Date dimension.

I created 2 straight tables for previous week and current week. You are able to see 7 days before selection, or any number of days after selection. Now i'm trying to calculate spend and that is where i hit do not understand the behavior.

My original thinking was, because i determined dimensions i don;t need to put anything extra in my measure calculations, but i guess i'm wrong.

Just in case picture is hard to understand here is my calculations for Dates for current week, previous week and spend:

Current week Date:

if( GetSelectedCount(Date)=0,

   if(Date >= Weekstart(Today(),0,-2) and Date <= Weekend(Today(),0,5),

      date(Date)

     )

, date(Date)

)

Previous week Date:

if( GetSelectedCount(Date)=0,

    if(Date >= Weekstart(Today(),-1,-2) and Date <= Weekend(Today(),-1,5),

    date(Date)

    )

, date(Date)

)

And i'm just using normal spend calculation:

sum({<fact_type={'sales_facts'}>}sales_amount_cc) /

sum({<fact_type={'pax_count_facts'}>} passenger_count)

So sum it up, requirements given to me, i need to show only 7 days for each table, additionally user should be able to pick any date and see selection date(s) in bottom table and week before selection date(s).

Any ideas and help would be much appreciated

Oredas

[JonnyPoole]

Hi there - have you considered using set analysis to set the date range ?  Performance (at scale) will typically be better with set statements vs. nested IFs.

[#3]

Heya Jonathan, But how i would go around having Date dimension  showing only for 7 days, but you gave me an idea, so let me get back to you to see if it works.

[#4]

Ok i tried couple other things, but my head is blank now.

Bottom line is i need two tables, which always show 7 days of previous week in one and 7 days of current week in another, and spend for current week would be up until today and 0 for future days until week end.

Any help would be much appreciated,

oredas

[sunny_talwar]

May be try to ignore selection in Date field in your expression since the dates are not handled in dimension. But do you make selections in any other date related fields as well such as month or year, I guess you might want to ignore them as well based on how things look...

Sum({<fact_type={'sales_facts'}, Date>} sales_amount_cc)/Sum({<fact_type={'pax_count_facts'}, Date>} passenger_count)

[#6]

Heya Sunny,

Let me see if i understand you correctly, leave date column as it is and in Spend calculation add to ignore Date dimension?

I did try your formula, but it doesn't seems to make a difference, i don't get any values for spend and my dates do not get recognized.

And i don't have any other selections, just Date.

Any other approach i should be looking into?

[sunny_talwar]

Have you used the correct date field? You need to make sure that the spelling and casing of your date field matches... Since Qlik View is Case Sensitive, myDate is a different field than MyDate or mydate. If that still doesn't work, would you be able to provide a sample for us to take a look at?

Best,

Sunny

[#8]

Hey Sunny,

Spelling is correct, as it is "Date" and nothing else. Sorry, but i'm not capable to provide sample, but everything you see in pictures provided above is all i'm using for this particular analysis.

Just to clarify, when i add ",Date" in spend calculation in table as you show above, without date range selection i see spend, but no dates are showing (shows "-"). When i select date range, i have range of dates from min to max, so it is not taking my previous week formula.

Sorry and thanks a lot for taking your time to solve this Sunny

Regards,

Oredas