Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

leni_balakrishn
Contributor III

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

Tags (1)
1 Solution

Accepted Solutions

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

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)

13 Replies

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

In your expressions, try something like:

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

Not applicable

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

Hard code the country name..

leni_balakrishn
Contributor III

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

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??

leni_balakrishn
Contributor III

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

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

MVP
MVP

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

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

leni_balakrishn
Contributor III

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

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.

leni_balakrishn
Contributor III

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

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

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

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)))

)

leni_balakrishn
Contributor III

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

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

Community Browser