Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

To show one day before data for a country and other with today's data

Hi all,

I have requirement where I need to show one day before data for switzerland country and today's data for other countries in a straight table.

The straight table will have columns as Country, Expr1, Expr2.

In that, the value for Expr1 & Expr2 for the country switzerland should show today()-1 and for other countries the values should be for today.

Kindly let me know your ideas on this.

Thanks,

Leni Balakrishnan

1 Solution

Accepted Solutions
Nicole-Smith

See the attached file.

In your load script I changed this line:

if(Content = 'B', Date((Date(Date,'YYYY-MM-DD')-1),'YYYY-MM-DD'),Date(Date,'YYYY-MM-DD')) as ReportDate

to

if(Content = 'B', Date((Date(Date,'YYYY-MM-DD')+1),'YYYY-MM-DD'),Date(Date,'YYYY-MM-DD')) as ReportDate

Then for the expression on the chart, I used:

sum({<ReportDate={"$(=date(max(ReportDate),'YYYY-MM-DD'))"}>}Value)

View solution in original post

13 Replies
Nicole-Smith

In your expressions, try something like:

if(Country = 'Switzerland', sum({<YourDateField={'$(=date(today()-1))'}>}Sales), sum({<YourDateField={'$(=today())'}>}Sales))

Not applicable
Author

Hard code the country name..

Anonymous
Not applicable
Author

Thank you for the solution.

Will this work if selection date is there?

Like when I navigate to that particular sheet the latest date will be selected and that date data will be dispalyed in the charts so if I include this expression will this work as I expected??

Anonymous
Not applicable
Author

But how it will give me one day before data.??

tresesco
MVP
MVP

You can replace today() with max(Date) in the said expression by Nicole Smith

Anonymous
Not applicable
Author

How to include the below expression into this.

(Sum(if(Group <> 'HYB',Qty010*((Cost * Currency.ExchangeRate)/ReportingCurrency.ExchangeRate),0))+

Sum (if(Group <> 'HYB',Qty1120*((Cost * Currency.ExchangeRate)/ReportingCurrency.ExchangeRate),0))+

Sum (if(Group <> 'HYB',Qty2130*((Cost * Currency.ExchangeRate)/ReportingCurrency.ExchangeRate),0)))

This is the expression I am using in the chart.

Anonymous
Not applicable
Author

I tried the above solution and it worked if there is a data for today & yesterday. But my requirement is,

when I update my qvd today it will not have the data for switzerland for that day and the data will not display in application so to resolve this we need to show one day before data alone for switzerland.

Like all countries will have today's data except swis.

Please suggest me a solution to resolve this.

Many thanks in advance

Nicole-Smith

Updating my expression:

if(Country = 'Switzerland', sum({<YourDateField={'$(=date(max(YourDateField,2)))'}>}Sales), sum({<YourDateField={'$(=date(max(YourDateField)))'}>}Sales))

So your expression should look something like:

if(Country = 'Switzerland,

(Sum({<YourDateField={'$(=date(max(YourDateField,2)))'}>}if(Group <> 'HYB',Qty010*((Cost * Currency.ExchangeRate)/ReportingCurrency.ExchangeRate),0))+

Sum ({<YourDateField={'$(=date(max(YourDateField,2)))'}>}if(Group <> 'HYB',Qty1120*((Cost * Currency.ExchangeRate)/ReportingCurrency.ExchangeRate),0))+

Sum ({<YourDateField={'$(=date(max(YourDateField,2)))'}>}if(Group <> 'HYB',Qty2130*((Cost * Currency.ExchangeRate)/ReportingCurrency.ExchangeRate),0))),

(Sum({<YourDateField={'$(=date(max(YourDateField)))'}>}if(Group <> 'HYB',Qty010*((Cost * Currency.ExchangeRate)/ReportingCurrency.ExchangeRate),0))+

Sum ({<YourDateField={'$(=date(max(YourDateField)))'}>}if(Group <> 'HYB',Qty1120*((Cost * Currency.ExchangeRate)/ReportingCurrency.ExchangeRate),0))+

Sum ({<YourDateField={'$(=date(max(YourDateField)))'}>}if(Group <> 'HYB',Qty2130*((Cost * Currency.ExchangeRate)/ReportingCurrency.ExchangeRate),0)))

)

Anonymous
Not applicable
Author

Thank you for the answer.

Can you please look into my above post and help me? Currently I am facing this issue.

I tried your logic and it worked but it didn't satisfies my requirement.

Thanks